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
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