Skip to Content

Formula not working in WEBI Cross Table


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)
Add a comment
10|10000 characters needed characters exceeded

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.