# 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

## Re: Exclude nulls in time average calculation

Try:

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

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

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

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

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

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

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