6 Replies Latest reply: Jan 24, 2013 8:36 AM by Krzysztof Adamski

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

• ###### 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...;

• ###### 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

• ###### Re: Running total of previous 5 days

Hi

use the above or top functions

above(sum(value),5)

• ###### 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()))

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

• ###### 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?