Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Exclude nulls in time average calculation

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

8 Replies
MVP
MVP

Re: Exclude nulls in time average calculation

Try:

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

Employee
Employee

Re: Exclude nulls in time average calculation

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

satyadev_j
Valued Contributor

Re: Exclude nulls in time average calculation

Something like below

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

Not applicable

Re: Exclude nulls in time average calculation

Hi Henric,

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

chauhans85
Esteemed Contributor

Re: Exclude nulls in time average calculation

     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

Employee
Employee

Re: Exclude nulls in time average calculation

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

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

HIC

Not applicable

Re: Exclude nulls in time average calculation

This throws the answer out

chauhans85
Esteemed Contributor

Re: Exclude nulls in time average calculation

did you got solution? or is there any query.

if yes then mark it helpful or correct .

else post your query back

Community Browser