Browsing through many (so many!) previous questions, including the Holiday & Workdays FAQ and every known combination of search terms here and on Google, I haven't found anything that fits my dilemma. If I have totally overlooked something or misunderstood, please forgive me, my brain is going in circles at this point.
(There's a lot of process I'm going to run through here; skip to the bottom of you want to get to the heart of the matter.)
Standard work order/incident/ticket process: date/timestamp for when it's created and when it's dispatched, responded to, and completed. SLAs are dependent on the priority level, and they're measured by business hours. Bear with me here, I know you're rolling your eyes because you've seen this before. But wait, there's more!
Convert creation date to business days - easy. Have a calc to adjust it to 8AM the next business day. No problemo.
(If you see anything in my calcs that can be improved for performance PLEASE let me know because I've been crashing Tableau as I get further down the line.)
So now we're starting on a business day, Monday-Friday, 8AM to 5PM. Perfect - and this is where most questions end, but mine haven't even begun.
Next we add our Dispatch SLA to that awesome business day creation date:
Here's where we start going to crazy town:
You can't recreate what you did for the creation date, because you can't roll the time over to 8AM, you have to add it on ending at 5PM and starting again at 8AM the next day.
If a work order is submitted at 2 PM and has a 240 minute turnaround for its SLA, that puts the target turnaround at 6PM. No problem, calculate how many minutes past 5PM and tack that on to 8AM the next business day for a target turnaround time of 9AM. Boom.
HOWEVER, if the target turnaround goes beyond 5PM the second day how can I loop this until it's done? For example, a ticket is submitted on 1/1/19 12PM with a turnaround time of 1,440 business minutes (3 business days). The calcs I have will do 5 hours on 1/1/19 and roll the remaining 19 hours over to start at 8AM 1/2/19 and end at 12PM on 1/4/19. (My math may be off, but you get the point. Again, brain. hurt. sorry.) If I did what I did with creation date, it would think the target date is during working hours, but it doesn't get credit for the hours between 5PM on 1/2/19 and 8AM on 1/3/19 or for the hours between 5PM on 1/3/19 and 8AM on 1/4/19.
Here is the absolute mess of a calc that has kinda sorta gotten me part way there:
So how the heck do I get it to keep rolling rolling rolling in 8-hour increments, down to the minute?
I'm going to scream if you tell me there's an easy way that's been out there all along that I missed. But you Tableau users are my people so I hope you'll forgive me and point me in the right direction.
If it makes any difference, I'm using a PostgreSQL connection.
Work Order Target Dates.twbx 56.8 KB