Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

How to calculate the Average days between start and end date

Hi Team,

My requirement is to calculate the average days between start date and end date for calendar days for below data set.

LOAD * INLINE [
Loan, Start Date, End Date
1, 07-Jan-19, 21-Jan-19
2, 24-Dec-19, 30-Jun-20
3, 04-Nov-19, 18-Feb-20
4, 28-Dec-18, 03-Jan-20
5, 11-Feb-20, 25-Feb-20
];

 

 

Labels (4)
4 Replies
jwjackso
Specialist III
Specialist III

 

Try the Aggr function

Avg(Aggr([End Date] - [Start Date],Loan))

kvr9
Creator
Creator
Author

Thanks Jwjackso,

Could you please let me know how to exclude null values from the calculation .

also why are we using the aggr function here?

jwjackso
Specialist III
Specialist III

The AGGR function returns a virtual table, in this case it is a table of the [End Date] - [Start Date] values.

To eliminate nulls, try this

Avg(Aggr({<[Start Date]={"*"},[End Date]={"*"}>} [End Date] - [Start Date],Loan))

JuanGerardo
Partner - Specialist
Partner - Specialist

This is a good solution, but it will consider when one date is null and the other not. To have records with both dates not null, i suggest using an intersection of both sets:

Avg(Aggr({<[Start Date]={"*"}> * <[End Date]={"*"}>} [End Date] - [Start Date],Loan))

And probably Aggr() is not necessary, depending on other dimensions and expressions in the same chart.

JG