BO Smoothing (recursion)

[webi2002]

I have a requirement that stomped me. Maybe a fresh perspective will solve the question. On a daily basis we take counts on tables and append them on to a flat table that consist of Date and count. Sometimes the count spikes above the average and in effect we can determine if there was a bad incoming file due to count increase. However I would like to correct this spikes within the report as follows:
Date Count
3/1/02 10
3/2/02 11
3/3/02 12
3/4/02 13
3/5/02 14
3/6/02 15
3/7/02 16
3/8/02 2
3/9/02 18
3/10/02 19
3/11/02 20
3/12/02 200
3/13/02 210
3/14/02 22
3/15/02 22
3/16/02 23
3/17/02 240
3/18/02 25
3/19/02 26

Would like to have a smooth line out of the cube in the fixed column
Date Count Fixed
3/1/02 10 10
3/2/02 11 11
3/3/02 12 12
3/4/02 13 13 <-- Formula
3/5/02 14 14
3/6/02 15 15
3/7/02 16 16
3/8/02 2 16
3/9/02 18 18
3/10/02 19 19
3/11/02 20 20
3/12/02 200 20
3/13/02 210 20
3/14/02 22 22
3/15/02 22 22
3/16/02 23 23
3/17/02 240 23
3/18/02 25 25
3/19/02 26 26

In Excel I can use the following formula
to smooth the line:
=IF(ABS(AVERAGE($C2:$C4)-$B5)<$D$1*C4,$B5,C4)
This formula works in excel assuming that the header fields are in the first row in the sheet and Date = Column A, Count = Column B, fixed = Column C.
The logic is taking the average of the last three counts minus the current count and having a tolerance level defined by the last value and determining if we need to use the previous of the fixed or the count value in order to smooth the line.
In Business Objects, I use the predefined formulas provided such as &quot;Previous(), Average(), Abs(), ...&quot; However I can not use the previous of the same function because Business Objects will not allow &quot;Looping&quot;.
In a sense this is a recursive function that works great in excel, however when attempting to do so in BO, then I did not find a way to do recursiveness.

If anyone has done something similar in a report or has a better idea to smooth the line, then please reply so that I can close the issue and see if BO can accomplish this without the use of &quot;Application Foundation&quot;.

 

[blom0344]

Hello Webi2000,

Your description is very clear, and I understand what you are trying to achieve (though I doubt whether I understand why you want such a peculiar 'smoothing' operation).

First of all, what you are doing is not actual smoothing, cause you are actually dumping high input values all together. The purpose of smoothing is to DIMINISH the impact of extreme values, cause you expect there relevance to be lower than normal values.

The previous function that BO offers in fact does just that and BO reporter is short on further statistical tools, which is a pity just the same.

Secondly, the nature of tables and crosstabs in BO do resemble a spreadsheet , so that I have users complaining that BO won't allow them to change data in a report. (But thats in fact a great asset). BO just displays data in a gridlike manner, whereas a spreadsheet is an actual collection of cells you can refer to (and thus gives you recursive tools) BO lets you address a column or a body of data, cause that's the smallest data-set available.

In your case , you expect a spreadsheet functionality (with read/write options) in a read-only reporting tool. I think your expectations were either too high or you are in need of a tool for statistical purposes. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[BreakerS]

You could try using VBA to process the data in the same way as the Running Average example on the BO website

David