Exclude nulls in time average calculation

[#1]

Hi there,

Below is the expression I have where I am averaging the time difference.

(AVG(Aggr([CE DATE] - [COR DATE],TRACE_NUMBER,TRACE_NUMBER)),' dd hh:mm')

I have been requested to not Average any values where [CE DATE]  is null.

Your soonest response will be highly appreciated.

Regards,

Mpho

[tresesco]

Try:

(AVG( {<[CE DATE]={*}>}Aggr([CE DATE] - [COR DATE],TRACE_NUMBER,TRACE_NUMBER)),' dd hh:mm')

[hic]

If you want the average counted over TRACE_NUMBER, you should probably use

     Sum([CE DATE] - [COR DATE]) / Count(distinct If(not IsNull([CE DATE]), TRACE_NUMBER))

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

If you should want to use the Aggr() function for this, you need an aggregation function as first parameter. It does not work the way you have written it.

HIC

[Anonymous]

Something like below

(AVG(Aggr(If(Len([CE DATE])>0, [CE DATE]) - [COR DATE],TRACE_NUMBER,TRACE_NUMBER)),' dd hh:mm')

[#5]

Hi Henric,

I get something out, but it doesn't format it to 'dd hh:mm' which i have tried. Please advise

[SunilChauhan]

     i have modified in henric code

Date(Sum([CE DATE] - [COR DATE]) / Count(distinct If(not IsNull([CE DATE]), TRACE_NUMBER)),'dd hh:mm')

may be that helps

[hic]

I think it would be better to format it as an interval:

     Interval( Sum ... , 'dd hh:mm')

HIC

[#8]

This throws the answer out

[SunilChauhan]

did you got solution? or is there any query.

if yes then mark it helpful or correct .

else post your query back