7 Replies Latest reply: Feb 19, 2014 4:05 AM by Ossi Venäläinen

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

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

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

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

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

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

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

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

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

Hi,

Try the below expression in your chart for Rolling3:

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

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

but  rquireed in script

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

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

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

Fabrice

• ###### Re: How to calculate a rolling total in script when multiple dimensions in data

Thanks. problem solved.