Rolling Average for past 10 days

Question

Hello Everyone-

We am trying to solve following logic to determine last 10 days of average resolution time. we are considering 2 shifts buckets 6 am-6 pm and 6 pm- 6 am(next day) . For example , please refer to the table on the left in the attached file which has following columns:

"REQUESTID", "CREATEDATE", "RESOLVEDATE", "RESOLUTIONTIME"

we would like to calculate the average resolution time of the requests which fell in the 2 buckets mentioned above  and publish a report daily. The twist in the problem is for calculating the 10 days (or any X days period) time for Night Bucket you have to go to the previous day because night shift starts at 6 pm . For example please refer to the table on the right in the same attached file on how to consider the time.

Will really appreciate any help on this topic.

Thank You.

TaktTime.xlsx

danrh · Accepted Answer

Something like this?

I'm attributing each occurrence to the date the shift started to account for the night shift and any occurrence that spans multiple days --- if that logic isn't right, you'll need to tweak the first Formula tool.  I then summarize the occurrences and resolution times by shift/day, and take a 10 day average using a Multi-Row Formula.  There are a few more tools here than I'd like, but I think it gets you pretty close to what you're looking for.  Take a look, post again if this isn't it or if something isn't clear.