Forum Discussion

vidya_ar's avatar
vidya_ar
Frequent Visitor
9 years ago

Calcuated working days

Can anyone can help how to calculate the each items average quantity per day per store.   I have 10 different items which is sold in the month of april and may .   Usually i calculate Total quant...
Need Help
Show and Tell
v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Hi vidya_ar,

 

Please provide us some sample data, and elaborate your expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

  • vidya_ar's avatar
    vidya_ar
    Frequent Visitor
    9 years ago

    Please find the Sample sheet.

     

    STORE NAME,ITEM NAME,QTY,MONTH,Working Days,per day per store
    STORE1,shirt,100,Jan-17,31,3.2
    STORE1,salwar,500,Jan-17,31,16.1
    STORE1,salwar-m,1234,Jan-17,31,39.8
    STORE1,salwar-l,567,Jan-17,31,18.3
    STORE1,salwar x,234,Jan-17,31,7.5
    STORE1,towel,111,Jan-17,31,3.6
    STORE2,vegitable,12,Jan-17,18,0.7
    STORE2,shirt,200,Jan-17,18,11.1
    STORE2,salwar,467,Jan-17,18,25.9
    STORE2,salwar-m,233,Jan-17,18,12.9
    STORE2,salwar-l,111,Jan-17,18,6.2
    STORE2,salwar x,121,Jan-17,18,6.7
    STORE2,towel,567,Jan-17,18,31.5
    STORE2,vegitable,22,Jan-17,18,1.2
    STORE1,shirt,56,Feb-17,28,2.0
    STORE1,salwar,11,Feb-17,28,0.4
    STORE1,salwar-m,2,Feb-17,28,0.1
    STORE1,salwar-l,567,Feb-17,28,20.3
    STORE1,salwar x,344,Feb-17,28,12.3
    STORE1,towel,66,Feb-17,28,2.4
    STORE2,vegitable,12,Feb-17,10,1.2
    STORE2,shirt,2,Feb-17,10,0.2
    STORE2,salwar,54,Feb-17,10,5.4
    STORE2,salwar-m,88,Feb-17,10,8.8
    STORE2,salwar-l,222,Feb-17,10,22.2
    STORE2,salwar x,888,Feb-17,10,88.8
    STORE2,towel,11,Feb-17,10,1.1
    STORE2,vegitable,11,Feb-17,10,1.1

     

     

     

     

     

     

    • v-caliao-msft's avatar
      v-caliao-msft
      Microsoft Employee
      9 years ago

      Hi vidya_ar,

       

      Store Name Item Name QTY Month Working Days Per day per store
      STORE1 shirt 100 17-Jan 31 3.2
      STORE1 salwar 500 17-Jan 31 16.1
      STORE1 salwar-m 1234 17-Jan 31 39.8
      STORE1 salwar-l 567 17-Jan 31 18.3
      STORE1 salwar x 234 17-Jan 31 7.5
      STORE1 towel 111 17-Jan 31 3.6
      STORE2 vegitable 12 17-Jan 18 0.7
      STORE2 shirt 200 17-Jan 18 11.1
      STORE2 salwar 467 17-Jan 18 25.9
      STORE2 salwar-m 233 17-Jan 18 12.9
      STORE2 salwar-l 111 17-Jan 18 6.2
      STORE2 salwar x 121 17-Jan 18 6.7
      STORE2 towel 567 17-Jan 18 31.5
      STORE2 vegitable 22 17-Jan 18 1.2
      STORE1 shirt 56 17-Feb 28 2
      STORE1 salwar 11 17-Feb 28 0.4
      STORE1 salwar-m 2 17-Feb 28 0.1
      STORE1 salwar-l 567 17-Feb 28 20.3
      STORE1 salwar x 344 17-Feb 28 12.3
      STORE1 towel 66 17-Feb 28 2.4
      STORE2 vegitable 12 17-Feb 10 1.2
      STORE2 shirt 2 17-Feb 10 0.2
      STORE2 salwar 54 17-Feb 10 5.4
      STORE2 salwar-m 88 17-Feb 10 8.8
      STORE2 salwar-l 222 17-Feb 10 22.2
      STORE2 salwar x 888 17-Feb 10 88.8
      STORE2 towel 11 17-Feb 10 1.1
      STORE2 vegitable 11 17-Feb 10 1.1

       

      For your sample data, the working days for STORE1 in Janauary in 31, and for STORE2 is 18, how do you get the value?

       

      Regards,

      Charlie Liao

      • vidya_ar's avatar
        vidya_ar
        Frequent Visitor
        9 years ago

        Hi

         

        Store1 Workign Days:

         

        Jan month -31 days

        Feb month-18 days

         

        Store 2 Workng Days:

        Jan month -28 days

        Feb month -10 days

         

        Working days should be common for all the items for their respective month.

         

        Final Result sample should be as belwo:

         

        Total Salwar per day for Jan and Feb month

        Total Shirt per day for Jan and Feb month