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