Exclude null fields from performance to date average

[boeloe]

Hi,
i'm battling with the following.
In a report containing an array for months of the year we have fields with null values (no data yet. new system, starting from jan2004) with a YTD performance totals after month 12.
How do i exclude the monthly totals with null values from the "performance_to_date" calculation. Also, how do i go about to only count the months that do contain valid values because the null fields shows up as "0" bearing in mind that i might have a valid "0" value.

Using CR9 with MySQL as the database.

Any help highly appreciated

Thanx

 

[hilfy]

You could work off of a couple of formulae:

{@IsValid}
If Not IsNull({performance field}) then 1 else 0

{@ValidMonths}
Sum({@IsValid}, {optional group field})

Instead of using the average function, you then divide the sum of the performance field by {@ValidMonths} to get your average.

-D