Runningsum over time period question - PELASE HELP

building reports webintelligence
1

I really hope someone can help me.
I need to calculate a daily running sum over a fixed time period.

So Each day I need to calculate the running sum of say the previous 3 days.

Date Daily SUMof prev 3 days
01/01/01 10 10
02/01/01 20 30
03/01/01 50 80
04/01/01 30 100
05/01/01 15 95
06/01/01 20 65
07/01/01 2 37

Trying to get it to do this in WebI is driving me crazy.
If I am missing something blindingly obvious please let me know but i have tried everything and looked at many many posts.

If you are then feeling in a helpful mood I am looking to move from this point to producing this in to a daily avg of the past 3 days, so:

Date Daily AVG prev 3 days
01/01/01 10 10
02/01/01 20 15
03/01/01 50 27
04/01/01 30 33
05/01/01 15 31
06/01/01 20 21
07/01/01 2 12

I know I can do this easily by adding a divide by 3 (’/3’) to the end of the running sum but not sure if there is a easier way to do this.

By the way I kept the time period to 3 days here so i can show example easily, I will be changing to 90 days when running this in my report but don’t think that would really affect the solution that much.

Thanks to all for firstly reading this and then again to anyone who can offer a solution.

Nick

Sinkerson (BOB member since 2009-07-30)

2

Imagine you have column

A B
1-1-2001 20
2-1-2001 30
3-1-2001 40
4-1-2001 50
5-1-2001 60

Now create a variable
=Previous()+Previous()+Previous()

I think this will help u in resolving ur issue…Kindly let me know

Peliyar (BOB member since 2008-05-26)

3

I m sorry the above one is not working…

Peliyar (BOB member since 2008-05-26)

4

if there are never any dates missing, and it’s sorted on the date column, you could try:

=(<Revenue>+Previous(<Revenue>)+Previous(Previous(<Revenue>)))/3

otherwise, I would try to create this as measures in the universe:

  1. runningsum for n periods (solution depends on the data model)
  2. number of occurences in n periods
    and then in the report divide them to create a correct average.

highandstoned :netherlands: (BOB member since 2005-08-01)