cancel
Showing results for
Did you mean:
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.

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)

• ### WEB Data

4 Replies
Specialist III

Try the Aggr function

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

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?

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

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