topic Re: Exclude nulls in time average calculation in QlikView

Exclude nulls in time average calculation

Fri, 27 Jun 2014 09:19:35 GMT

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

Re: Exclude nulls in time average calculation

tresB — Fri, 27 Jun 2014 09:21:58 GMT

Try:

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

Re: Exclude nulls in time average calculation

hic — Fri, 27 Jun 2014 09:27:05 GMT

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

Re: Exclude nulls in time average calculation

Anonymous — Fri, 27 Jun 2014 09:28:12 GMT

Something like below

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

Re: Exclude nulls in time average calculation

Fri, 27 Jun 2014 10:10:38 GMT

Hi Henric,

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

Re: Exclude nulls in time average calculation

SunilChauhan — Fri, 27 Jun 2014 10:13:48 GMT

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

Re: Exclude nulls in time average calculation

hic — Fri, 27 Jun 2014 10:18:01 GMT

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

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

HIC

Re: Exclude nulls in time average calculation

Tue, 01 Jul 2014 08:21:32 GMT

This throws the answer out

Re: Exclude nulls in time average calculation

SunilChauhan — Tue, 01 Jul 2014 08:26:46 GMT

did you got solution? or is there any query.

if yes then mark it helpful or correct .

else post your query back