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:
- For the WholeWeeks, create a multi-row formula with a large if/elseif statement. if wholeweeks = 1, then current row, elseif wholeweeks = 2 then current + next, etc.
- For fraction, use "offset" concept which has been shown in other alteryx posts.
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!
