Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Running total of previous 5 days

Hi guys,

I will appreciate your help. I am trying to build an expression in pivot table to sum the values of last 5 days. As I load only values from working days weekends are not an issue.

I have managed to get the value from 5th day in the past, but I do not know how to sum the range based on a date dimension. Moreover this one works only if you select at least 5 days

=rangesum(above(TOTAL sum(Value), 5,RowNo()))

6 Replies
MVP & Luminary
MVP & Luminary

Re: Running total of previous 5 days

If your not using the date dimension in your chart and select only one day you could use a set expression

sum({<MyDate={">=$(=only(MyDate)-4)"}>} Value).

Another option is to calculate the 5-day running total in the script during load with the rangesum and peek functions.

MyTable:

load Date,Value,...etc...,

rangesum(Value, peek('Value',-1),peek('Value',-2),peek('Value',-3),peek('Value',-4)) as RunningTotal

from ...somewhere...;


talk is cheap, supply exceeds demand
Not applicable

Re: Running total of previous 5 days

Unfortunately I have to use date dimension.

I like the solution to calculate the running total in the load script, but I get some ridiculous numbers. Should I aggregate somehow with dimensions specified using aggr function?

I want to achieve something as following:

filters: product, customer

date              value          running value

1/18/2013        1                   1

1/21/2013        2                   3

1/22/2013        3                   6

1/23/2013        4                  10

1/24/2013        5                  15

1/25/2013        6                  20

Not applicable

Re: Running total of previous 5 days

Hi

use the above or top functions

above(sum(value),5)

MVP & Luminary
MVP & Luminary

Re: Running total of previous 5 days

You might get ridiculous numbers if your data isn't sorted by Date. If you add an order by date to the load statement you'll probably see numbers that make more sense.


talk is cheap, supply exceeds demand
Not applicable

Re: Running total of previous 5 days

no results, just '-'

I got some results when using formula as following, but this one is simply ignoring dimensions

=rangesum(above(TOTAL sum(Value), 5,Rowno()))

Not applicable

Re: Running total of previous 5 days

I had to use a resident table, since I load data from QVDs, but still numbers are too high. How to calculate this rangesum per dimensions?