topic Averages ignoring zero values in QlikView

Averages ignoring zero values

Thu, 30 Jun 2011 07:02:24 GMT

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

Averages ignoring zero values

erichshiino — Thu, 30 Jun 2011 13:02:15 GMT

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

Averages ignoring zero values

Fri, 01 Jul 2011 10:40:09 GMT

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