Hi,
I'm looking for a way to calculate a rolling average when the user can select the "offset" and the "count" for that rolling average using Inputs Variable, like in Above() + RangeAvg() functions.
I know how to use the input variable and how to use the Above\RangeAvg functions, however, I want to display the rolling average values also for my first row in my selection (or my only row in case I chose a single month), assuming that I have the data of previous months in my data load.
for example, if my entire data load is:
| Month |
Qty |
| 1 |
3 |
| 2 |
4 |
| 3 |
5 |
| 4 |
3 |
| 5 |
7 |
| 6 |
5 |
In case the user chose offset of 0 and count of 3, and didn't filter any month (which I know how to do with Above\RangeAvg):
| Month |
Qty |
RollingAvg |
| 1 |
3 |
- |
| 2 |
4 |
- |
| 3 |
5 |
(5+4+3)/3 |
| 4 |
3 |
(3+5+4)/3 |
| 5 |
7 |
(7+3+5)/3 |
| 6 |
5 |
(5+7+3)/3 |
In case the user chose offset of 0 and count of 3, and filter months 4-6 (not sure how to do that):
| Month |
Qty |
RollingAvg |
| 4 |
3 |
(3+5+4)/3 |
| 5 |
7 |
(7+3+5)/3 |
| 6 |
5 |
(5+7+3)/3 |
In case the user chose offset of 0 and count of 3, and filter month 6 (not sure how to do that):
| Month |
Qty |
RollingAvg |
| 6 |
5 |
(5+7+3)/3 |
I assume that in this case Above() won't work, but is there other way to achieve it and still have it dynamic for the user to place the offset and the count for the rolling average (comparing to "As-Of" table which I understand that the Offset and Count are static)?
Thanks
Dror
