I’m looking for some help.
I’m trying to calculate a Monthly Moving Average (MMA) in a BO report, i.e. the average value on the last 6 months for each month. The only solution that I have found is to make a big (and dirty) nested IF: …
If (=6) Then (
( in (, ) where (=1)+ in (, ) where (=2)+ in (, ) where (=3)+ in (, ) where (=4)+ in (, ) where (=5)+ in (, ) where (=6))/6 ) else
If (=7) Then …
It’s still messy, but easier to maintain since you don’t hard code a particular month number. One formula works for every month.
There are other issues; you need to check for missing months and decide how they should affect your calculations. In other words, if February is missing (no data) then what is the definition of your moving average? In your formula, February will be treated as zero. In my example the formula will simply go back in time one extra month.
I mentioned some tricks using the previous() function in a presentation at the BusinessObjects user conference this year… if you like, I can send you a sample report based on the Island Resorts universe that shows one way to do this calculation.
In a message dated 98-12-17 10:27:21 EST, you write:
I’m trying to calculate a Monthly Moving Average (MMA) in a BO report, i.e.
the average value on the last 6 months for each month. The only solution that I have found is to make a big (and dirty) nested IF: …
If (=6) Then (
( in (, ) where (=1)+ in (, ) where (=2)+ in (, ) where (=3)+ in (, ) where (=4)+ in (, ) where (=5)+ in (, ) where (=6))/6 ) else
If (=7) Then …