Averages ignoring zero values

[#1]

Hi,

I recently posted a question on Averages which fixed my problem at the time. However, what I have found is that it does not work for some of my calculations, as it omits the zero records

I have a table that looks as follows:

                                                     Week_No   |  1   |  2   |  3  |  4  |  AVG 

Employee ID  |   Employee Name  | 

1                         Ben                                        2      2      3     3     2.5

2                         Alan                                       2      2      0     2     2

What I am expecting to see, is that Alan's average should be: 6/4 =  1.5, not 6/3 = 2, so that it does not omit the zero values

The calculation I am using in this case is:

count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct WEEK_NO)

Week_No is being used as the dimension with 'Partial Sums' selected to provide the average       

Many Thanks

Ben

[erichshiino]

In your table, the zero value really exist? Or it is created in the table?

Maybe you could change the expression a little bit, so all the employees have the same number of weeks as reference:

count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct WEEK_NO)count({$<WAGE_CODE_NAME>}  ACCOUNT_DATE / count(distinct total WEEK_NO)

Hope this helps,

Erich

[#3]

Hi Erich

Thanks for the reply

The zero value does not exist, they are null records. I suspect I need a way to create values, so it forces an entry on every week? Is there a way of doing this?

Many Thanks

Ben