Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

ProductPeriodVersionAmountRolling 3
A1Actual1010
A2Actual1020
A3Actual1030
A4Actual1030
A1Budget1111
A2Budget1122
A3Budget1133
A4Budget1133
A5Budget1133
B1Actual2020
B2Actual2040
B3Actual2060
B4Actual2060

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

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

7 Replies
Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

but  rquireed in script

Not applicable

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

Thanks. problem solved.

Community Browser