dinsdag, februari 16, 2010

Calculating average in Business Objects

At a customer, someone asked this particular question : In a crosstab, if certain lines return empties, how do you calculate average while evaluating the empties as 0 ?

As it is, when you calculate the average with the regular function, BO will not take into account the missing information.
2007 2008 2009
Dog 10 10 10
Cat 10 10
Horse 10
Pony 10 10
Average 10 10 10

where it should be
Average 10(40/4) 2,5(10/4) 7,5 (30/4)

The solution to this problem is : contexts.

First, we need to count the number of animals

Count 4 1 3

performing a regular count will not give us the right result.

Count() will return a different number for each column. So, we force the count with "in" Count() in Report -- that will return 4 in each column.
Count 4 4 4

So, now we have our formula :

Sum()/Count() in Report

Cheers folks


Geen opmerkingen: