Sum the total sales for every 12 weeks

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.

book1.xlsx

Transformation

Preparation

Accepted answers

AngelosPachis

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

example2.yxmd

All comments

Qiu

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

0123-sreenivasateja.yxmd

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

example.yxmd

AngelosPachis

Any updates on this @stj1120 ?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703