Discussion Board for collaboration on QlikView Scripting.
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.
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.-
Go to Solution.
Use peek('Field' , -N) to get N lines before (1 is the default)
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
See a doc I have published on LOAD: http://community.qlik.com/docs/DOC-5698Chapter 3.4
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()
I sorted (Order By) the list and managed to calculate a cumulative sums by adding a following row:
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
Try the below expression in your chart for Rolling3:
You may also modify the model so that you create rolling totals and YTD:
but rquireed in script
Thanks. problem solved.