Queues and Utilization in Tableau: Part 1 – Queues

About once a week I see a post on the Tableau Forum that ends up being a queue or utilization problem. Let me give you some examples:

I have work orders on each row with an open and (possibly) a close date on each row. I want to plot a chart showing dates and the number of open cases.

This is a queue problem – items enter the queue and at some point leave it. Or we have:

I have a number of treatment rooms in my clinic. Each row of data has the time the patient entered and the time the patient left. I want to understand by hour of the day what rooms are busiest.

This is a utilization problem. Both can be solved in different ways and have multiple solutions depending what the end result needs to be and what the data looks like. This post looks at queues – the essential difference is that you only care about dates where something happens and don’t care about dates with no data. Hopefully that will become clearer as they are subtlety different.

Here’s my data on Sheet 1. You can see we have some cases that were not closed.

 

 

 

 

 

 

 

At the moment there’s no way of getting to that chart of open cases across the year. The Single Table SQL generated by Tableau is simply:

SELECT [Sheet1$].[Closed] AS [Closed],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 [Sheet1$].[Opened] AS [Opened]
FROM [Sheet1$]

We cannot plot the queue because open dates and closed dates are separate dimensions. We could reshape the data in Excel (using the add-in if we wanted) to look like this:

 

 

 

 

 

 

 

 

 

 

 

But that’s not always practical if the data is more than a one-off or isn’t coming from Excel. It’s easier to reshape the data using Custom SQL. Instead of doing a single table connection to the data we choose Custom SQL:

 

 

 

 

 

 

 

 

 

 

 

 

And then edit the SQL that Tableau created for the single table connection so that we pull open and closed dates onto separate lines but under one dimension ([Date]) and have a counting mechanism we’ll call “Number Open”:

SELECT [Sheet1$].[Opened] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Opened" as [Date Type],
 1 as [Number Opened]
FROM [Sheet1$]
union all
SELECT [Sheet1$].[Closed] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Closed" as [Date Type],
 -1 as [Number Opened]
FROM [Sheet1$]

We don’t really need both [Date Type] and [Number Opened] as we could create calculations in Tableau if we only had one of them, but I like to keep them there for my sanity. Now the data looks much like our reshaped data – we run though the data set once collecting all of the open dates as a date, and marking them Open ([Date Type]) and increment the number open by one, then run through the data again (bottom paragraph), add the new rows on (union all), mark them as close dates and decrease the count of open by 1. Note when you union the columns must be in the same order, so I collect date first, regardless of it being open or closed.

Now we’re beginning to get somewhere. I can create this chart by using our date, and the quick table calculation “Running Total”. Because we have the Number Open measure, the running total takes the line up and down as we need it:

 

 

 

 

 

 

 

 

 

 

It’s not great, but all of the data I need is there. I can see that in early Feb there were a max of 5 cases opened, and that by the last date I still have 2 cases open. Note I had to filter null dates out to get the chart to show correctly. Tableau cannot draw stepped charts; the lines are connected by slopes. We can rectify this by adding some more points to create the steps. We need to add a point just before each date (start or finish) to correctly draw each step. As I’m using Excel I can use the dateadd in the custom SQL. For other sources you may have to use a simple subtraction or different function. We’ll add a point one second before each date:

SELECT [Sheet1$].[Opened] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Opened" as [Date Type],
 1 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT [Sheet1$].[Closed] AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Closed" as [Date Type],
 -1 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT DATEADD('s',-1,[Sheet1$].[Opened]) AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Padding" as [Date Type],
 0 as [Number Opened]
 FROM [Sheet1$]
 union all
 SELECT DATEADD('s',-1,[Sheet1$].[Closed]) AS [Date],
 [Sheet1$].[Department] AS [Department],
 [Sheet1$].[IT Person] AS [IT Person],
 "Padding" as [Date Type],
 0 as [Number Opened]
 FROM [Sheet1$]

Our datasource is now four times as large, but we can now draw a chart in exactly the same way as before but with the steps:

 

 

 

 

 

 

 

 

 

 

 

I can add Department or the IT Person to the view on rows (or both to color and size) and break the chart correctly. This may be the end point for many people and the chart they need. However, because of the running sum it’s difficult to reuse this data elsewhere and get the view you want. When I’ve done this before I’ve copied the summary data (view data) out, pasted it into Excel, and brought it in as a new source. This is not an ideal solution. Even if I do this, what if I want know about the open cases on Feb 20th? There are no cases that open or close on that date so there is no data point for it. This has now become a utilization problem, and needs to be solved in a different way.

Here’s the data file: Work orders and the Tableau Workbook: Queue

I would love to take credit for these methods, but they are a combination of solutions created on the forums  by Joe Mako, Richard Leeke, and Jonathan Drummey. 

 

4 Comments


  1. Joe Mako
    Oct 28, 2012

    Good post, thanks for putting this together.

    The method that you show here for making a stepped line is less than ideal. See my comment at http://community.tableausoftware.com/message/135833#135833 for a better method for making a stepped line in Tableau.


  2. Shawn Wallwork
    Oct 29, 2012

    Thanks for this Alex. Custom SQL is finally starting to make a bit of sense to me. Well written.


  3. ikejames
    Nov 01, 2012

    Great post! So awesome to see something new here!


  4. Ashley
    Jan 10, 2013

    Thanks for posting this, Alex. This could have a lot of appeal to our healthcare customers.

Leave a Reply