topic Re: How to calculate a rolling total in script when multiple dimensions in data in QlikView

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

Tue, 18 Feb 2014 12:13:16 GMT

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

Tue, 18 Feb 2014 12:16:50 GMT

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

Tue, 18 Feb 2014 12:59:29 GMT

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

Tue, 18 Feb 2014 13:04:31 GMT

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

Tue, 18 Feb 2014 13:09:57 GMT

Hi,

Try the below expression in your chart for Rolling3:

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

Tue, 18 Feb 2014 13:16:19 GMT

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

Fabrice

Tue, 18 Feb 2014 13:20:34 GMT

but rquireed in script

Wed, 19 Feb 2014 09:05:15 GMT

Thanks. problem solved.