Running total of previous 5 days

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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

[#4]

Hi

use the above or top functions

above(sum(value),5)

[Gysbert_Wassenaar]

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.

[#6]

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

[#7]

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?