Calculate Average - only if not null

[#1]

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???)

[Anil_Babu_Samineni]

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

[sunny_talwar]

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'

[swuehl · Accepted solution]

Maybe

=Avg(Date2-Date1)

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

[Anonymous]

Hi,

May be like this

='Average' & chr(13) &

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

[swuehl]

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))

[#7]

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