Skip to Content

Formula not working in WEBI Cross Table

Dec 29, 2017 at 12:06 AM


avatar image


I have a requirement to determine a 3 period rolling average, whenever I have no values in subsequent periods. See the example below:

1. Periods 10-13 have 0s in the 3rd column.

2. I need to take the values from 3 previous periods (highlighted in yellow) and calculate the average;

3. The value of 1.70980 (green box) is that rolling average. I'll take the maximum value of those 3 rolling averages.

4. Finally that value is getting repeated for periods 10-13 in the final column, which is what I need.

This works beautifully in a simple row table, but is giving me headache on the crosstable, which is what my requirement dictates.

I have some scenarios, like the one below, where all values for all periods will be 0, so the result should be 0, like below in my row table:

But in my cross table, it's doing something different (see red)

Notice how for my first example, it's working correctly (green box)

Below are the formulas being used:

Moving Average Calculation: =If(IsNull([Pds ago OF Act Rate (Round)])) Then Previous(Previous(RunningSum([Pds ago OF Act Rate (Round)]);0)-Previous(RunningSum([Pds ago OF Act Rate (Round)]);3);1)/3

Pds ago OF Act Rate w/ Moving Average and Round=Max([Moving Average Calculation]) In ([Budget].[Product Group].[Product Group - Key])

Finally in the cell:

=If(IsNull([Pds ago OF Act Rate (Round)])) Then [Pds ago OF Act Rate w/ Moving Average and Round] Else [Pds ago OF Act Rate (Round)]

This is BOBJ 4.2 SP3, and my underlying data is from S/4 using CDS Views. My consumption view behaves like a BEx query therefore I'm using the OLAP BICS connection.

I'd appreciate any help on why the different behaviors on the different table types.

Thank you,


1.png (27.9 kB)
1.png (16.1 kB)
1.png (21.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers