Greetings. I've got an alteryx-induced headache over this one. Any ideas are much appreciated.
Data looks like this:
WeekStartDate | Quantity | WeeksToSum |
1/1/2017 | 110 | 2.1 |
1/8/2017 | 107 | 2.8 |
1/15/2017 | 107 | 3.5 |
1/22/2017 | 117 | 3.5 |
1/29/2017 | 120 | 2 |
2/5/2017 | 101 | 1.8 |
2/12/2017 | 118 | 3.1 |
The goal is to sum Quantity over the number of rows given in WeeksToSum, including the current row. Expected results are as follows (with some helper columns added which split WeeksToSum into whole and fraction).
WeekStartDate | Quantity | WeeksToSum | WholeWeeks | PartialWeeks | ExpectedResults |
1/1/2017 | 110 | 2.1 | 2 | 0.1 | 227.7 |
1/8/2017 | 107 | 2.8 | 2 | 0.8 | 307.6 |
1/15/2017 | 107 | 3.5 | 3 | 0.5 | 394.5 |
1/22/2017 | 117 | 3.5 | 3 | 0.5 | 397 |
1/29/2017 | 120 | 2 | 2 | 0 | 221 |
2/5/2017 | 101 | 1.8 | 1 | 0.8 | 195.4 |
2/12/2017 | 118 | 3.1 | 3 | 0.1 | 118 |
The best idea we've come up with is:
To make the problem harder, above is just for 1 product. The real data set has many products. So a group by issue also applies.
Thanks for any ideas!
Solved! Go to Solution.
Hi @MLD,
Thanks for your question! The best solution would be to use a batch macro. Here is a great training to get you started. This will allow you to "Group By" product. I would also recommend that you use a filter to filters the rows of data you need and then use the summarize tool. .
Hope this helps!
Amelia
@MLD,
I went with a Generating rows solution. This was a pretty fun one to think through. Thanks for the challenge.
Solution attached.
Best,
MSalvage
EDIT: Updated solution to account for multiple products.
Hi @MLD,
I've attached a workflow here which contains a batch macro which will achieve your desired results (no long multi-row formula necessary). This macro is currently configured to 'GroupBy' the 'Product' thus this workflow will work with multiple products. Please note that your 'RecordID' should be set for each product, hence my using s Multi-Row formula tool to do this.
Let me know if you have any questions!
Amelia
MSalvage - Unbelievably elegant! And such a fast reply. Thanks!
AmeliaG - I figured a macro solution had to exist to simplify the long (and not bullet proof) multi-row formula. Thanks!
@MSalvage Very elegant solution, indeed!!