8 Replies Latest reply: Jul 1, 2014 4:26 AM by Sunil Chauhan

# 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

• ###### Re: Exclude nulls in time average calculation

Try:

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

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

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

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

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

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

This throws the answer out

• ###### 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

• ###### 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')