Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
load *, if(peek(Period)=Period ,Value +peek(Value),Value) as Rolling_value,
;
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
];
Hi,
in the graph
[rolling value] = if(rowno()=1,value,above([rolling value]) + value)
regards
Thank you, unfortunately this does not work. It seems to add successive pairs of values,
i.e.
Period | Week | Sum(Value) | sum(Rolling_value) | |
1 | 1 | 25 | 25 | week 1 |
1 | 2 | 30 | 55 | week 1+2 |
1 | 3 | 27 | 57 | week 2+3 |
1 | 4 | 23 | 50 | week 3+4 |
2 | 5 | 28 | 28 | etc. |
Hi,
Try this
Load *,
if(Period=peek(Period),peek(Rolling_Value)+Value,Value) as Rolling_Value
Resident Table
Order By Period,Week;
Regards,
Devyanshu
Hi,
just rolling_value , not sum(rolling_value) !