topic Re: How to make Avg ignore null values in QlikView

How to make Avg ignore null values

Fri, 01 Jul 2011 15:32:59 GMT

Does anyone know how to make the AVG calculation in a chart expression ignore null values? For example, if I have three values: 3, 5, and null; then I would want the average to be 4, not 2.67.

How to make Avg ignore null values

deepakk — Fri, 01 Jul 2011 15:34:37 GMT

hi Tim,

Try this out.

avg ( if(isnull(Fieldname))=0,Fieldname)).

Deepak

Re: How to make Avg ignore null values

johnw — Fri, 01 Jul 2011 23:20:40 GMT

That's strange, I was sure that avg() already ignored nulls. I just tried rangeavg(3,5,null()) and got 4, so rangeavg() ignores nulls as expected. OK, built a quick little example, and avg() ignored nulls as well. Maybe a version difference? That's in V9SR6. Same behavior in V10SR2. I think your problem may be something different than you think it is.

Re: How to make Avg ignore null values

Wed, 06 Jul 2011 15:39:40 GMT

Yes, sorry, my mistake. The Avg function was taking an average of several Sum functions (and all that within an Aggr). It is the Sum that is converting nulls to zeros and causing trouble. Thanks for your response.