How to calculate a rolling total in script when multiple dimensions in data

[#1]

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.-

[#2]

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

[#3]

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

[#4 · Accepted solution]

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

[#5]

Hi,

Try the below expression in your chart for Rolling3:

RangeSum(Above(sum(Amount),0,3))

[#6]

You may also modify the model so that you create rolling totals and YTD:

http://community.qlik.com/docs/DOC-4821

Fabrice

[#7]

but  rquireed in script

[#8]

Thanks. problem solved.