Out of the things I learned from being a manager (http://serwer1996697.home.pl/meowbi/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:

ticket001;2016-01-01 11:00:54;2016-01-04 13:00:54;"DE"
ticket002;2016-07-04 14:00:54;2016-07-05 12:00:54;"UK"
ticket003;2016-05-02 09:00:54;2016-05-11 12:30:54;"UK"
ticket004;2016-03-11 13:00:54;2016-03-15 12:00:54;"UK"
ticket005;2016-01-21 15:00:54;2016-01-22 13:30:54;"UK"
ticket006;2016-02-26 19:00:54;2016-03-01 12:15:54;"UK"
ticket007;2016-06-09 13:00:54;2016-06-09 15:45:54;"UK"
ticket008;2016-03-01 11:00:54;2016-03-01 12:00:54;"UK"
ticket009;2016-04-22 21:00:54;2016-05-02 12:00:54;"UK"
ticket010;2016-02-01 19:00:54;2016-02-03 12:00:54;"UK"
ticket011;2016-08-19 11:00:54;2016-08-22 15:00:54;"DE"
ticket012;2016-08-15 13:37:54;2016-08-16 15:00:54;"DE"
ticket013;2016-07-07 11:00:54;2016-07-08 14:15:54;"DE"
ticket014;2016-06-10 11:00:54;2016-06-13 12:35:54;"DE"
ticket015;2016-05-13 11:00:54;2016-05-16 11:46:54;"DE"
ticket016;2016-04-18 11:00:54;2016-04-19 12:00:54;"DE"

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):

//work days on the day of Start Date
IF 1 < DATEPART('weekday',[created_date_daylight]) AND DATEPART('weekday',[created_date_daylight]) < 7 //only for weekdays
AND ([created_date_daylight] - DATETRUNC('day',[created_date_daylight])) < 1/24*17.5  //and only for events starting before work end
AND DATETRUNC('day',[created_date_daylight]) < DATETRUNC('day',[solved_date_daylight]) //ignore same-day events, they are counted with End Date
//work days elapsed since Start Date (or 09:00, whichever is later) to work end time (17:30), as fraction of 9 hour work day
		(DATETRUNC('day',[created_date_daylight]) + 1/24*17.5) -
			DATETRUNC('day',[created_date_daylight]) + 1/24*9
ELSE 0 //zero for weekends

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

//number of *whole* (24 hours) work days between two dates
//working days from Start Date to the end of that week
IF DATEPART('weekday',[created_date_daylight]) == 7 //0 for Sat
//0 for start and end on the same week, to avoid double-counting,
//these days will be counted with End Date
OR DATETRUNC('week',[created_date_daylight]) == DATETRUNC('week',[solved_date_daylight])
ELSE 7 - DATEPART('weekday',[created_date_daylight]) - 1

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

//work days from whole weeks between
CASE SIGN(DATETRUNC('week',[solved_date_daylight]) - (DATETRUNC('week',[created_date_daylight])+7))
WHEN -1 THEN 0 //set to 0 if whole weeks between is negative
ELSE INT((DATETRUNC('week',[solved_date_daylight]) - (DATETRUNC('week',[created_date_daylight])+7))/7)*5

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

IF DATEPART('weekday',[solved_date_daylight]) == 1 OR DATETRUNC('day',[created_date_daylight]) == DATETRUNC('day',[solved_date_daylight])
THEN 0 //set 0 for Sundays and same-day start/end
//number of days
//from: either Monday, or Start Date if it is later than Monday
//to: either End Date, or Saturday (excluding) whichever is earlier (to exclude Sat)
ELSE INT(MIN(DATETRUNC('day',[solved_date_daylight]),DATETRUNC('week',[solved_date_daylight])+6) - MAX(DATETRUNC('week',[solved_date_daylight])+1,DATETRUNC('day',[created_date_daylight])+1))

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

//work days on the day of End Date
IF 1 < DATEPART('weekday',[solved_date_daylight]) AND DATEPART('weekday',[solved_date_daylight]) < 7 //only for weekdays
AND ([solved_date_daylight] - DATETRUNC('day',[solved_date_daylight])) > 1/24*9 
THEN //and only for events ending after work start
//work days elapsed from 9:00 until Solve Date (or 17:30, whichever is earlier), as fraction of 9 hour work day
	MIN([solved_date_daylight],DATETRUNC('day',[solved_date_daylight]) + 1/24*17.5) - 
	MAX([created_date_daylight],(DATETRUNC('day',[solved_date_daylight]) + 1/24*9))  
ELSE 0 //zero for weekends

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

// 03 Oct - holiday
IF date([created_date_daylight]) = date("2014-10-03")// AND date("2014-10-03") <= [Solved at]
Then -1+([created_date_daylight]-(datetrunc('day',[created_date_daylight])+1/24*9))/((17.5-9)/24)
ELSE 0 // nothing to deduct or add

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:

if date([Created Date]) >= dateadd('month',3,datetrunc('year',[Created Date]))-(datepart('weekday',dateadd('month',3,datetrunc('year',[Created Date])))-1)
and date([Created Date]) <= dateadd('month',10,datetrunc('year',[Created Date]))-(datepart('weekday',dateadd('month',10,datetrunc('year',[Created Date])))-1)
then dateadd('hour',-1,[Created Date])
else [Created Date] end

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:

IIF([Business Hours (09:00 - 17:30) - DE]*3600 % 60 == 60,0,[Business Hours (09:00 - 17:30) - DE]*3600 % 60)// seconds
+ IIF(INT([Business Hours (09:00 - 17:30) - DE]*3600/60) %60 == 60, 0, INT([Business Hours (09:00 - 17:30) - DE]*3600/60) %60) * 100 //minutes
+ INT([Business Hours (09:00 - 17:30) - DE]*3600/3600) * 10000 //hours

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: