Skip to Content
0
Former Member
Dec 21, 2012 at 03:34 PM

Hiding rows in a table but using these rows in a RUNNINGSUM / RUNNINGAVG function

34 Views

Reporting Guru's,

I am challenged with kind of a problem in web intelligence. I am using BO 4.0 and the problem is related to filtering out rows from a result table.

I need to display revenue by month for 12 months following any given month, the month is defined by a parameter.

Next to the revenue 2 extra columns are needed, one displaying a running sum and one a running average, not really a running sum, but a rolling 12 month window... ex 2012-01-01 = 2011-01 - 2012-01, 2012-02 = 2011-02 - 2012-02 and so on ....

The problem is that the first month (= to the parameter) needs to have the cumulated 12 months revenue value as its running total value.

This is easily done as follows:

change the custom query with

"where date between dateadd(MONTH, -12, @prompt(month)) and dateadd(MONTH, 12, @prompt(month))

--> this fills the table with records from ex. 2011-01-01 and 2012-12-31 when @prompt(month) = 2012-01-01

When the formula "= [revenue] + previous(revenue; 1) + previous([revenue]; 2) + ..... + previous([revenue];12)" is applied the running total value is correct.

Problem are the users. They will not settle with the rows where month < @prompt(month)....

filtering out the months < @prompt(month) leaves me with a running total starting at @prompt(month)

All I want to do is suppress the rows where month < @prompt(month) AND NOT FILTER THEM OUT. In a way filter out the records < @prompt(month) AFTER the running total has been calculated.

@prompt(month) = 2012-01

month revenue 12 mth rolling window

2011-01 100 100 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-02 110 210 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-03 120 330 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2001-04 130 460 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-05 140 600 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-06 150 750 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-07 160 910 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-08 170 1080 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-09 180 1260 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-10 190 1450 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-11 200 1650 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2011-12 210 1860 -->DO NOT DISPLAY BUT TAKE INTO ACCOUNT FOR CALCULATION OF 2012-01 VALUE

2012-01 220 2080

2012-02 230 2310

2012-03 240 2550

2012-04 250 2800

...

HELP PLEASE....

Sinc,

Eric Janssens