Calculating turnaround times with overlapping date ranges

[dukane24]

Hello,

I am attempting to calculate turnaround times for requirements that are requested by form.  This is working great except for situations where the date ranges overlap - a form can have multiple concurrent requirements that have been requested, and I don't want the turnaround times to "stack".

For example, using the data below, the requirement TAT for form 1 would be 36 days.  This is easy because the date ranges to not overlap.  But using my current method, form 2 shows a requirement TAT of 81 days.  This is not correct because the date ranges overlap.  I would like the total TAT in this situation to calculate to 50 days.

Here's the formula I'm currently using: =avg(aggr(sum(distinct [Requirement Received Date]-[Requirement Request Date]),[FormNum]))

I appreciate any help you can provide.  Thank you!

FormNum	Requirement Type	Requirement Requested	Requirement Received	TAT (days)
1	A	1/1/2017	2/1/2017	31
1	B	2/15/2017	2/20/2017	5
2	C	1/1/2017	2/1/2017	31
2	D	1/15/2017	2/20/2017	36

[maxgro]

If I understand, I would make a new field in the script.

Load the data ordered by FormNum and Requested date;

when you have a row with the same FormNum as the previous (you can use peek) check the requested date of current row vs received date of previous row (you can use peek)

- requested > received --> new field is requested

- requested <= received --> new field is received +1

In chart, use received date and the new calculated field

[dukane24]

Thank you - Unfortunately there is no guarantee that the rows for a particular form will be consecutive.  This is a table with a lot of activity.