Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.