Hello, hope someone can help with this please.
I have a table of values which are stored by financial period (not month) and week. Each period has a number of weeks, e.g. period 1 always has 4 weeks, but period 2 always has 5 weeks. The weeks just roll forward, they don’t restart at 1 each time a period starts. There are 12 periods and 52 weeks per year.
E.g.
Load * inline [
Period,Week,Value
1, 1, 25.00
1, 2, 30.00
1, 3, 27.00
1, 4, 23.00
2, 5, 28.00
2, 6, 29.00
2, 7, 22.00
2, 8, 32.00
2, 9, 21.00
3, 10, 19.00
];
I have to show this table as a Qlik Sense table in the same format as above but I need another column that shows the value as a rolling value that restarts at each period, i.e.
| Period | Week | Value | Rolling Value | Notes |
|---|
1 | 1 | 25.00 | 25.00 | week 1 |
| 1 | 2 | 30.00 | 55.00 | week 1+2 |
| 1 | 3 | 27.00 | 82.00 | week 1+2+3 |
| 1 | 4 | 23.00 | 105.00 | week 1+2+3+4 |
| 2 | 5 | 28.00 | 28.00 | new period, so just week 5 |
| 2 | 6 | 29.00 | 57.00 | week 5+6 |
| 2 | 7 | 22.00 | 79.00 | week 5+6+7 |
| 2 | 8 | 32.00 | 111.00 | week 5+6+7+8 |
| 2 | 9 | 21.00 | 132.00 | week 5+6+7+8+9 |
| 3 | 10 | 19.00 | 19.00 | new period, so just week 10 etc. |
I think I need to use rangesum(above(Value)) in some way but can’t work out how.
Thanks for any help.
