Sum the total sales for every 12 weeks

[stj1120]

Hi all,

I need help in calculating the sum of sales for every 12 weeks per each Material. 

Lets say, this is week 4 and it should sum the sales values of each material for the next 11 weeks until week 16 along with week 4 which will be total 12 weeks. If we are in week 5 then it should sum the sales values from week 5 to week 17.

How do we calculate this dynamically? 

Kindly help. Thank you.

Best,

Teja.

[Qiu]

@stj1120 
Assume the first week in your file is the start week.

[AngelosPachis]

Hi Teja ( @stj1120 ),

I have created a workflow that I think answers your question.

What the workflow does, in the first formula tool, you convert the Inventory week from a yyyyww format to an actual date and then you dynamically check at which inventory week we are currently at. 

Then, in the multi-row formula tool, for each Material Number, you are counting how many weeks are left in the future in your dataset; Note that this count won't restart at the end of the year, because I wasn't sure if that was a requirement. However, if you want the week count to restart for every Material Number and year, you can group it on "Matno" and "Monday start of year".

Then it was just a matter of grouping the different weeks together in groups of 12 and summing as necessary. 

In the results, Null "Groups of weeks" mean that these weeks are in the past. I have also added the first and last date for each group, thought that would be useful.

Let me know if that works for you.

Regards,

Angelos

[AngelosPachis]

Any updates on this @stj1120 ?

[stj1120]

hi @AngelosPachis and @Qiu 

sorry for the delay response.

Thanks for your work. But somehow, the solution is not appropriate.

We should only sum the Sales based on each material from the Current week to until next 11 weeks. 

Lets say this is week 4. So it should sum the sales based on each material from week 4 to week 15 and again when the flow runs in  week 5 it should sum the sales values from week 5 to week 16. 

Like wise, it has to sum the sales value for each material considering every current week and its value for the next 11 weeks.

Hope you understand what I said above. I have been trying but, no luck.

Kindly help me with the solution.

Thanks,

Teja.

[AngelosPachis · Accepted solution]

Hi @stj1120,

You were right, I thought you wanted to create groups of 12 weeks and then sum. Now it's clear, I have amended the workflow to only sum the values for the next 12 weeks from the current week.

Let me know if that worked for you please.

Regards,

Angelos