Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I cannot figure out how to calculate rolling sum in script..
Found a solution at the discussion board for data with just time dimension and amount but I get confused when there are other dimensions.
Product | Period | Version | Amount | Rolling 3 |
A | 1 | Actual | 10 | 10 |
A | 2 | Actual | 10 | 20 |
A | 3 | Actual | 10 | 30 |
A | 4 | Actual | 10 | 30 |
A | 1 | Budget | 11 | 11 |
A | 2 | Budget | 11 | 22 |
A | 3 | Budget | 11 | 33 |
A | 4 | Budget | 11 | 33 |
A | 5 | Budget | 11 | 33 |
B | 1 | Actual | 20 | 20 |
B | 2 | Actual | 20 | 40 |
B | 3 | Actual | 20 | 60 |
B | 4 | Actual | 20 | 60 |
Any hint would be highly appreciated on how to calculate this Rolling 3 column in script when the source data does not contain it.
Cannot attach files here, don't know why.-
Use peek('Field' , -N) to get N lines before (1 is the default)
peek('CumulAmount')-peek('Amount', -3)+Amount
or peek('Amount', -2) + peek('Amount',, -1)+ Amount as CumulAmount
But the test can be even more difficult to do to be sure to do (or not) the cumul
Fabrice
See a doc I have published on LOAD: http://community.qlik.com/docs/DOC-5698
Chapter 3.4
In short:
1) you need to sort the rows (ORDER BY)
2) you need to test that you may do the cumul: if(peek('A')=A And peek('B')=B, OK I can compute, I cannot compute)
3) you use rangesum()
Fabrice
Thank you!
I sorted (Order By) the list and managed to calculate a cumulative sums by adding a following row:
if(peek('Product')=Product And peek('Version')=Version, Rangesum(Amount, Peek('CumulAmount')), Amount) as CumulAmount, |
Any ideas on how to accumulate only 3 values?
or how to pick cumulative value from rowno() - 3? so it could be subtracted from cumulative value
Use peek('Field' , -N) to get N lines before (1 is the default)
peek('CumulAmount')-peek('Amount', -3)+Amount
or peek('Amount', -2) + peek('Amount',, -1)+ Amount as CumulAmount
But the test can be even more difficult to do to be sure to do (or not) the cumul
Fabrice
Hi,
Try the below expression in your chart for Rolling3:
RangeSum(Above(sum(Amount),0,3))
You may also modify the model so that you create rolling totals and YTD:
http://community.qlik.com/docs/DOC-4821
Fabrice
but rquireed in script
Thanks. problem solved.