AGGR (of days - between two date fields) against another field - but want to restrict to a rolling period (ie 12 months)

[Daryn]

Hi,

I am using (correctly or incorrectly)...

=avg(aggr(([Date Out])-([Date In]), [Batch]))

To get the average amount of days an item is onsite.

This reports okay, but I want to restrict it to perhaps a rolling 12 month period (or less).

I tried:

=avg(aggr(( ( [Date Out.autoCalendar.Date] >= (Addmonths ( Today (),-12)))- [Date In.autoCalendar.Date] >= (Addmonths ( Today (),-12))), [Batch]))

But that didn't appear to work correctly. My date is formatted as DD.MM.YYYY

Any help appreciated, 

Regards Daryn

[Daryn · Accepted solution]

Solved it I think with:

avg(aggr(if([Date In.autoCalendar.Date] >= addyears(max([Date In.autoCalendar.Date]), -1), ([Date Out] - [Date In]), null), [Batch]))