topic Re: Sum the total sales for every 12 weeks in Alteryx Designer Desktop Discussions

Sum the total sales for every 12 weeks

stj1120 — Sat, 23 Jan 2021 07:18:55 GMT

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.

Re: Sum the total sales for every 12 weeks

Qiu — Sat, 23 Jan 2021 08:24:09 GMT

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

Re: Sum the total sales for every 12 weeks

AngelosPachis — Sat, 23 Jan 2021 09:15:11 GMT

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

Re: Sum the total sales for every 12 weeks

AngelosPachis — Wed, 27 Jan 2021 06:56:40 GMT

Any updates on this @stj1120 ?

Re: Sum the total sales for every 12 weeks

stj1120 — Wed, 27 Jan 2021 15:52:02 GMT

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.

Re: Sum the total sales for every 12 weeks

AngelosPachis — Wed, 27 Jan 2021 16:29:26 GMT

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