Rolling Average for past 10 days

[MD2050]

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.

[danrh · Accepted solution]

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.

[MD2050]

Thank you @danrh-

Really appreciate your help. 

i did replied earlier to your post asking a follow up question , apparently it didn't get posted. In the code you shared , it was deducting one day from those requestId's which were created on or after 6 pm which was causing those request id's to fall in wrong bucket. I added another shiftdate code to fix the issue.

IF
DateTimeHour([CREATEDDATE])>= 18 and DateTimeHour([CREATEDDATE])<=24 then
[CREATEDDATE] else [ShiftDate] endif

Thank you.