Business Hours calculation in Tableau

Out of the things I learned from being a manager (http://www.meowbi.com/2016/09/11/5-things-i-learned-being-first-time-manager/) is that performance measuring is one of the most important success factors. With few tricks, Tableau can be a tool suited for such purpose.

Back in the days I was asked to create a Tableau report that will provide vital information regarding Business Hours spent for solving tickets in support department. Sounds like an easy task but, as always, devil was in details (especially when official Tableau solution is not always working properly http://kb.tableau.com/articles/knowledgebase/calculating-the-number-of-business-days).

Target department is divided into 2 divisions: UK and DE. Both work in different time slots: UK 9:00-18:00 and DE 9:00 – 17:30. It means that those 2 teams will have to have different Business Hours calculations.

The solution

I created a very simple CSV file using dummy data to sketch minimal requirements for calculations:

As you can see it looks like a raw database dump without any calculations. Only assumption I will be taking is that tickets can be closed only during working hours (however they can be created at any time).

I must admit that this case was pretty hard to solve. Hard enough for me to look for support on Tableau forums. Solution came fromĀ Dimitri Blyumin and it was adopted by me to fit needs.

Idea behind the solution was to calculate float/decimal values of Business days spent on certain ticket and then recalculate it to Business Hours and present it in human readable form. As last 2 steps are mostly matter of way of displaying, the first one is most complex. It consists of 5 parts which are simply added on to another. First and last components are focused on calculating day fraction (create time till end of the day and start of the day till solve time) and 2nd, 3rd and 4th checks whole days between start and end dates:

1.Calculate number of days for events created Monday (weekday 2) till Friday (weekday 6) and before work end and show it as a fraction of whole working day (1/24*17.5-9):

2.Calculate number of days from start date till end of the Start Date’s week:

3.Calculate number of whole working weeks (x5 days) between dates – 0 if it’s the same week:

4.Calculate number of working days from beginning of that week to the End Date:

5.For tickets solved on weekday calculate Business day fraction between beginning of solve day till actual solve time:

6.Last but not least – remove fraction of the day responsible for the holiday period:

You can see clearly couple of patterns:

  • 1/24*9 – indicates 9 pieces of day representing starting date
  • ((17.5-9)/24) – representing real period of work in the working day. This is true for team working from 9:00 to 17:30

You probably also noted that fields used in the calculation contain _daylight suffix. Data used in this example is fetched from the UTC based system but teams work in daylight saving time affected timezones. To make sure that everything is aligned with reality, start and end dates must be altered to match daylight saving time. This is done by simple if function:

During final testing, it turned out that showing duration in time format is not exactly perfect. Initial solution was to simply add number of seconds calculated in Business Hours to dummy date and just show the time part of this date. It works great for tickets that took less than 1 day to solve as there is no dummy date to use for 0 day (there’s no 1990-01-00). Solution came from long term Tableau guru – Jonathan. It’s bit rough and not very sexy but it looks like it’s the only way to solve the issue. Idea is to take number of seconds and then divide it by 60 seconds increments and catch the modulo (rest from division).

Example using DE team Business Hours calculation:

In the calculation above, by changing values of 9 and 17.5 to desired work start and end, you can alter this solution to meet your requirements. Of course to keep this example as simple as possible working hours are hardcoded in the calculated field. You can easily change it to parameter or other variable to skip some of the other fields and to have a single value for ticket earlier.

photo by Jordan Whitfield:
https://unsplash.com/collections/329549/signs?photo=sm3Ub_IJKQg

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz