topic Re: null values in average in QlikView

null values in average

rcorcoran — Wed, 22 Oct 2014 08:41:43 GMT

Hi How can I make the following exclude the null or 0 values. It is giving me an average over all even if value is null whereas I want an average of just where there are values greater than 0??

Any help appreciated

Pick(Match(ValueList('Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

'Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

num(((Avg({$<[Acquired Infection]={4},ReferredMM={3}>}ActualValue)))), '##0.0')

Re: null values in average

anbu1984 — Wed, 22 Oct 2014 08:50:03 GMT

=num(((Avg({$<[Acquired Infection]={4},ReferredMM={3}>}(If(ActualValue,ActualValue))))), '##0.0')

Re: null values in average

Gysbert_Wassenaar — Wed, 22 Oct 2014 08:55:38 GMT

Pick(Match(ValueList('Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

'Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

num(((Avg({$<[Acquired Infection]={4},ReferredMM={3},ActualValue={'>0'}>}ActualValue)))), '##0.0')

Re: null values in average

rcorcoran — Wed, 22 Oct 2014 08:59:07 GMT

Both of the solutions above work thanks so much to you both

Re: null values in average

rcorcoran — Wed, 22 Oct 2014 09:00:26 GMT

Thanks anbu works great

Rhona Corcoran

Re: null values in average

rcorcoran — Wed, 22 Oct 2014 09:00:52 GMT

Thanks Gysbert works great

Re: null values in average

rcorcoran — Wed, 22 Oct 2014 09:01:18 GMT

Thanks Anbu works great