Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to make Avg ignore null values

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.

3 Replies
deepakk
Partner - Specialist III
Partner - Specialist III

hi Tim,

Try this out.

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

Deepak

johnw
Champion III
Champion III

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.

Not applicable
Author

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.