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

[Former Member]

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

[jyothirmayee_s]

Hi,

Suppression of rows and columns doesn;t work in WebI 4.0. I heard it supports in FP3. May be you can try testing it on FP3.

Thanks,

Jothi

[Former Member]

Can we try something with Alerters? Like, crearte an alerter to make cell heith to minimum possible and matches with the backgroung color when month < @prompt(month). I know this also will create some issues with formatting cell. Please check if it works.

Regards

Ram C