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(<Animal>) will return a different number for each column. So, we force the count with "in" Count(<Animal>) in Report -- that will return 4 in each column.

Count

4

4

4

 

So, now we have our formula :

Sum(<Number of Animals>)/Count(<Animal>) in Report

Cheers folks

Binabik