8 Replies Latest reply on Feb 15, 2019 5:13 PM by Dan Cory

    Yet another ticket SLA/business day question - with a twist?

    Lindsay Reed

      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.

        • 1. Re: Yet another ticket SLA/business day question - with a twist?
          Prasanna R

          Hi Lindsay,

           

          That was quiet an explanation It was like a good conversation

           

          So here goes mine.. I had a similar case and I had documented this so its just simply copy and paste and attaching the files for you. Please go through and see if I am able to steer you in the right direction,

           

          When you want to calculate the difference between 2 dates we can use "DATEDIFF" function; it could be the difference in days or hours, but there is no standard function to exclude outside office hours and week-ends like "Networkdays" function in excel.

           

          To achieve this we need to use a couple of calculated fields.

           

          Requirement:

          1) Start Date: We need the field to have date and time

          2) End Date: We need the field to have date and time

           

          Note:

          I have attached the sample workbook and dataset so that you can copy paste the calculated fields to your workbook (when you do this the parameters will be moved automatically). You will find the explanations below.

           

          Step 1:

          We need to first adjust the time stamp for 'Start Date' in such a way that the start time and end time are within the working hours. [Starting Hour] and [Ending Hour] are the parameters that will help user enter their own working hours. The time should be in 24 hours format. Eg. 7 am as "7" and 7 pm as "19".

          Calculated Field Name: "cal start date time"

           

           

          Step 2:

          we do the same for 'End Date'.

          Calculated Field Name: "cal end date time"

           

          Step 3:

          We need to alter the start date to be in the window of the working days. [Week Off - Day 1] and [Week Off - Day 2] are the parameters that will help user enter their own week-off days. Week-offs should be back to back days like "Sat and Sun" and not "Sun and Sat" or "Sat and Mon".

          Calculated Field Name: "new start date"

           

           

          Step 4:

          Do the same as Step 3 for 'End Date'.

          Calculated Field Name: "new end date"

           

           

          Step 5:

          Now that we have the date and time within the working hours, we calculate the difference in hours. We just need the absolute of the time difference hence we use ABS. Also we are checking if the difference in minutes is more than 30 then we add an hour to the total.

          Calculated Field Name: "Hour Diff"

           

          Step 6:

          Similarly we calculate the difference in days by checking if the start date and end data are in same year or weeks, or in different years or weeks.

          Calculated Field Name: "Date Diff"

           

           

          Step 7:

          With [Hour Diff] and [Date Diff] we calculate the difference in hours.

          Calculated Field Name: "Working Hours"

           

          Best Regards

          RP

           

          *If this helps, then please mark it Helpful and Correct to close this thread

          • 2. Re: Yet another ticket SLA/business day question - with a twist?
            Lindsay Reed

            Thanks Prasana, but this doesn't quite address the problem.

             

            • I shifted Actual Creation Date to business hours with the Work Order Creation Date Business Hours calc.  For any ticket submitted outside of business hours (8AM to 5PM, Monday-Friday), no one's going to see it until 8AM the next business day.  So we're starting within business hours for all tickets now.

             

            • The Creation Business Days + Dispatch Target adds the minutes allowed between creation and dispatch.  If the minutes exceed business hours, the remaining minutes roll over to the next business day.

             

            • For an example of where we currently are: A ticket is submitted at 1/1/19 6:45 PM, its creation date is shifted to 1/2/19 8 AM.  A tech is expected to be dispatched within 660 minutes (11 hours).  That would put the target dispatch date as 1/2/19 7 PM.  We now have 2 hours beyond the 1/2/19 business day for the tech to complete the job.  The tech gets credit for those two hours, so the target date can't shift to 8 AM the next business day, it has to include those extra two hours.  The target dispatch date is now 1/3/19 10 AM (8 AM + (7 PM-5 PM)).  So far, so good.

             

            • The problem starts with any ticket that has a target dispatch date that goes beyond 5PM the next business day.

             

            • If the ticket in the above example was created at 4:30 PM on 1/2/19, its target dispatch date is 3:30 PM on 1/3/19, before accounting for the overnight hours.  The tech works 30 minutes from 4:30 PM to 5 PM on 1/2/19 and picks it back up 8 AM on 1/3/19.  By 5 PM on 1/3/19, the tech has only worked 570 minutes out of the 660.  The remaining 90 minutes have to roll over to start at 8 AM on 1/4/19 and end at 9:30 AM.

             

            • This will compound as we calculate the additional targets.  For the target response date, it will be added to the shifted dispatch target.  A ticket that has only a 30 minute target dispatch time may have a 40 hour target response time, or vice versa.  Once response date is shifted, we have to do the same thing for completion target, which could be 1800 working hours (30 business days).  At this point Tableau says "Eff you" and crashes.

             

            I think I'm looking for something that can loop each time a ticket exceeds business hours.  I can picture it in SQL, but have no idea how to do it in Tableau.

             

            My head is going in circles now so I hope that makes sense.

            • 3. Re: Yet another ticket SLA/business day question - with a twist?
              Dan Cory

              A few notes on your calcs:

              You use DATEADD('hour',8,DATEADD('day',2... Since you know there are 24 hours in a day, just add the right number of hours (32 in this case).

              For "Friday after working hours", in your second calc you check again that the day was Friday, but this is unnecessary.

              Dispatch Target (minutes) should be a CASE statement instead of IF/THEN. Some of your other IF/THEN chains might be better written as CASEs as well.

              Your fourth calculation has DATETRUNC('minute' even though you already did that in the prior calculation and then just added minutes to it.

               

              There isn't way to loop in Tableau because there isn't any way to loop in declarative SQL. I can imagine a couple of possible solutions:

              1. If you know that you'll never roll over more than a few times, you can probably just write all the cases. This would be a bit of a pain, but probably doable. But you mention there can be 30 business day tickets, so that would be long. Your sample workbook only shows up to 9 business day tickets.

              2. You could write a stored procedure in your database.

              3. You could build a table in your database with all the working minutes and number them. Then you can just join to this table to get the end date you need.

                   3a. This table might be a bit large if you did working minutes, so you might need to do working hours instead. In that case I think you could use calcs to handle the minutes.

               

              Dan

              1 of 1 people found this helpful
              • 4. Re: Yet another ticket SLA/business day question - with a twist?
                Dan Cory

                I think there's another option:

                4. If you don't need the target date, but just to know whether your actual date met the SLA, then you can calculate the elapsed business minutes and compare to the SLA. But I think you want the target date.

                • 5. Re: Yet another ticket SLA/business day question - with a twist?
                  Dan Cory

                  After thinking this through a few more times, I realized there is a way to do it without a separate table and really quite simple calculations.

                   

                  The basic idea is to think about an alternate timeline where working minutes are sequential. So Monday morning at 8:00AM comes right after Friday afternoon at 4:59PM. Then you just need two functions - one to convert from a real time to the alternate timeline and one to convert from the alternate timeline to real time. Luckily both of these are pretty simple.

                   

                  You also need a "zero-point" on the alternate timeline to base conversions off. To make everything simple, we want this point to be somewhere that we can convert back and forth easily. A Sunday morning at midnight is a good place on both timelines. I used 2019-12-30 (which is a Sunday) but any Sunday would work.

                   

                  To convert from real time to the alternate timeline:

                  DATEDIFF('week',#2018-12-30#,[Actual Date]) * (5 * 9 * 60) + // Compute the number of weeks since the zero point and multiply by the number of working minutes in a week

                  MIN(MAX((DATEPART('weekday',[Actual Date])-2),0),5) * (9 * 60) + // Compute the number of working days since the start of the week and multiply by the number of working minutes in a day.

                  MIN(MAX((DATEPART('hour',[Actual Date])-8)*60+DATEPART('minute',[Actual Date]),0),9*60) // Compute the number of working minutes since the start of the day.

                  In the second line, we go from days that go from 1...7 to days from -1...6 and then to days from 0...5. This accounts for weekends.

                  In the third line, we go from minutes that go from 0...24*60 to minutes from -8*60...16*60 and then to 0...9*60. This accounts for working only from 8:00 to 8+9 = 17:00 (5 PM).

                   

                  To convert from the alternate timeline to real time:

                  DATEADD('minute',([Target Minute] % (9*60))+8*60, // Compute the number of minutes in the day. Shift to start day at 8:00.

                  DATEADD('day',(INT([Target Minute]/(9*60)) % 5)+1, // Compute the number of the days in the week. Shift to start week on Monday.

                  DATEADD('week',INT([Target Minute]/(5*9*60)),#2018-12-30#))) // Compute the number of weeks since zero-point.

                   

                  Once you have both conversion formulas, convert any date to the alternate timeline, add in the number of minutes you've promised the customer, and then convert back.

                   

                  This lets arithmetic handle all the looping that you wanted to do.

                   

                  One subtle point is that these calculations ignore seconds, so 8:00:15 will get treated the same as 8:00:00. If you want some other rules for seconds, you can adjust the first formula to account for it.

                   

                  This same technique will work for any other working hours that repeats both daily and weekly. With a little more effort it could also handle working hours that repeats weekly but has different schedules each day. Unfortunately it doesn't extend very well to handling intermittent holidays like Christmas.

                   

                  I attached a workbook with the data you originally posted.

                   

                  Dan

                  • 8. Re: Yet another ticket SLA/business day question - with a twist?
                    Dan Cory

                    I was working on another problem and I realized I have the conversion _to_ the alternate timeline off by a little bit. If the start time is during the day on the weekend, then the minutes from the weekend were incorrectly counted.

                     

                    The correct formula is as follows:

                    DATEDIFF('week',#2018-12-30#,[Actual Date]) * (5 * ((5+12) - 8) * 60) + // Compute the number of weeks since the zero point and multiply by the number of working minutes in a week

                    MIN(MAX((DATEPART('weekday',[Actual Date])-2) * (((5+12) - 8) * 60) +MIN(MAX((DATEPART('hour',[Actual Date])-8)*60+DATEPART('minute',[Actual Date]),0),((5+12) - 8)*60) ,0),5 * ((5+12) - 8)*60) // Compute the number of working days since the start of the week and multiply by the number of working minutes in a day.

                     

                    Basically, you have to bring the calculation of the working minutes in the day inside the MIN/MAX for the day of the week.

                    It's not quite as pretty as the original version but still not too bad.

                     

                    I also wrote out (5+12) - 8 just to make it a tiny bit easier to adjust if your working hours are different than the 8 am - 5 pm in your case.

                     

                    Sorry for any problems this may have caused.

                     

                    Dan