topic Ignore null values while calculaitng average in QlikView

Ignore null values while calculaitng average

markgraham123 — Wed, 18 May 2016 16:46:14 GMT

Hi,

Can someone pls tell me how to ignore null values while calculating average??

Al though we can do it by suppressing null value....

i'm displaying it in text box due to requirement.

Please find the attachment.

Any help is highly appreciated.

I wanna eliminate in text box but not in the script please.

sunny_talwar — Wed, 18 May 2016 16:48:32 GMT

Right now the Date is not Null, it is '-'. For this sample, try this:

='Average '&Num(Avg(Aggr(Count({<Date -= {'-'}>}Distinct User),Date)), '#,##.00')

For actual null, you can try this:

='Average '&Num(Avg(Aggr(Count({<Date = {'*'}>}Distinct User),Date)), '#,##.00')

markgraham123 — Wed, 18 May 2016 16:54:57 GMT

Sunny,

It worked.

You are awesome. I should have used the set analysis..

Thanks again brother.