topic Average when value <> 0 in QlikView

Average when value <> 0

Mon, 25 Jan 2010 17:45:46 GMT

I want a function to calculate average, but only when the value is <> 0.

Numbers: 100, 0, 200

AVG(Number): 100 (100+0+200 / 3)
I want 100+200 / 2 = 150

Thank in advance!

stephencredmond — Mon, 25 Jan 2010 17:56:58 GMT

Hi,

Use a set to exclude zero:

=Avg({< F1 -= {0}>} F1)

Stephen

Mon, 25 Jan 2010 17:57:54 GMT

Use Set Analysis:

Avg({<Numbers = {'>0'}>} Numbers)

EDIT: Didn't see Stephen's reply when I posted. His will work for you.

Mon, 25 Jan 2010 18:01:44 GMT

sum(Number) / count( if(Number>0,Number))

stephencredmond — Mon, 25 Jan 2010 18:11:07 GMT

Thanks for the question, it inspired a blog post:

BTW to the other guys - your ">0" set implies that all the numbers will be positive.

Stephen