Hello,

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,

Marcelo

## Add a comment