topic Calculate Average - only if not null in QlikView

Calculate Average - only if not null

Wed, 25 Nov 2020 16:16:04 GMT

Please see the file attached.

I am trying to calculate average (see text object) number of days between Date1 and Date2 but only for items that have both dates

Not sure how to do it (set analysis, if statement???)

Re: Calculate Average - only if not null

Anil_Babu_Samineni — Tue, 09 May 2017 14:09:40 GMT

Seems working, Why you want to do in set analysis?

Re: Calculate Average - only if not null

sunny_talwar — Tue, 09 May 2017 14:11:53 GMT

May be this

='Average' & chr(13) & num(sum(Date2-Date1)/Count({<Date1 = {"=Len(Trim(Date1)) > 0"}, Date2 = {"=Len(Trim(Date2)) > 0"}>}Item),'0.0') & ' days'

or if they are true nulls, then this

='Average' & chr(13) & num(sum(Date2-Date1)/Count({<Date1 = {'*'}, Date2 = {'*'}>}Item),'0.0') & ' days'

Re: Calculate Average - only if not null

swuehl — Tue, 09 May 2017 14:12:57 GMT

Maybe

=Avg(Date2-Date1)

If one of the dates is not a number, the date calculation returns NULL, and Avg() does not consider NULL

Re: Calculate Average - only if not null

Anonymous — Tue, 09 May 2017 14:17:02 GMT

Hi,

May be like this

='Average' & chr(13) &

num(sum({<Date2-={''},Date1-={''}>}Date2-Date1)/Count({<Date2-={''},Date1-={''}>}Item),'0.0') & ' days'

Re: Calculate Average - only if not null

swuehl — Tue, 09 May 2017 14:24:28 GMT

Also note that Item 17 appears two times in your sample data set.

It won't show up in your bar chart, because your expression

=Date2-Date1

does not return an unambiguous answer for this item. The suggested average calculations will consider this item though, if you want to exclude it, you can also use

=Avg(Aggr( Date2-Date1, Item))

Re: Calculate Average - only if not null

Tue, 09 May 2017 14:26:00 GMT

Thank you Stefan. The simplest solution is often the most accurate.