How to calculate the Average days between start and end date

[kvr9]

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
];

[jwjackso]

Try the Aggr function

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

[kvr9]

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]

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]

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