Day 17: Utilization or Step Charts

Over the next few days we're going to look at a couple different types of out of the box visualizations.  Today we're going to look at one that helps us understand more about process flows and resource utilization - It's called a Utilization Chart or Step Chart an it's easier to make than you think.

Learn About Tableau

The idea for this came from a recent project where I helped a client see how many assignments they planned to work on for the upcoming year, day by day.  I took a start date, and an end date and used a Utilization Chart to stack the assignments on top of each other during their active times.  This is a similar representation of it (and our goal for today):

The above shows the number of open orders (orders where the order date has occurred but the ship date is yet to come) or active orders.  All of this comes from our favorite, Superstore Sales.  I do want to give credit where it's due.  When I did the project I knew the visualization that I wanted to create but I didn't know how to do it, or what it was called.  I began searching and literally stumbled on to this blog post by Data Driven Consulting and once I saw their solution it made perfect sense.

If you're ready let's jump on in.

The first thing that you need to know is that this trick is basically completed before you start your visualization.  Basically all of the magic happens when you bring the data in using Custom SQL.  Before I show you how and explain it you should know a couple things:
1) Using this solution is going to increase the number of rows in your data by four times;
2) It uses Union All which is very inefficient; 
3) Given 1 and 2 above, Joe Mako suggests this solution instead.  Frankly, I prefer the one I show here.  You say potato, I say tomato, and that's why God makes colors.

To get the step effect for each order we need to show four points, when it goes up (order goes in ie start date), when it goes down (order is complete ie ship date), and a point just before the up and down that holds the value.  Therefore we're going to create a fields in the data, the first is called "Number Opened" that will be +1 when it's an Order Date, -1 when it's a ship date, and 0 for the Padding that keep the previous number held.  All of this comes together when you take the running sum of Number Opened in Tableau.  The other is called Date.  This is the contains the Order Date and the Ship Date, and the Padding dates that occur 1 second before the Order Date and Shiping Date (again, this is to give the step effect). 

Here we go take the data set and trim it down to just the dimensions and measure you might care about, then add the other fields discussed above.  Union All each of the four different cuts and your Custom SQL should look like this:
SELECT 
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Region] AS [Region],
  [Orders$].[Sales] AS [Sales],
  [Orders$].[Order Date] AS [Date],
  "Order Date" as [Date Type],
  1 as [Number Opened]
FROM [Orders$]

Union All

Select   
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Region] AS [Region],
  [Orders$].[Sales] AS [Sales],
  [Orders$].[Ship Date] AS [Date],
  "Ship Date" as [Date Type],
  -1 as [Number Opened]
FROM [Orders$]

Union All

Select 
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Region] AS [Region],
  [Orders$].[Sales] AS [Sales],
  DATEADD('s',-1,[Orders$].[Ship Date]) AS [Date],
  "Padding" as [Date Type],
  0 as [Number Opened]
FROM [Orders$]

Union All

Select 
  [Orders$].[Order ID] AS [Order ID],
  [Orders$].[Product Category] AS [Product Category],
  [Orders$].[Region] AS [Region],
  [Orders$].[Sales] AS [Sales],
  DATEADD('s',-1,[Orders$].[Order Date]) AS [Date],
  "Padding" as [Date Type],
  0 as [Number Opened]

FROM [Orders$]

Once you have this together, rest is rather easy.  Bring that data source into Tableau.  Fire Date on to Columns (exact Date, to the second) and add Number Opened to rows.  Right click and select running sum and you should now see this (I truncated the dates showing based on this calculated field - "If [Date]<#11/1/2013# then #11/1/2013# else [Date] end" and put this field on the columns self) :

Add in "Product Category" to both the rows shelf and the color shelf and BOOM:

You can now see the number of active orders going for each product category on any given day over the course of time shown.  Pretty cool huh?

Learn About Tableau

Hasta manana - 
Nelson