Tableau Doctor: 1 Column, 2 Dates? Use Custom SQL

I am a huge fan of doing things the right way instead of using workarounds or shortcuts. Tableau is an incredible tool for solving problems, and using the functionality within Tableau, I have rarely found a problem unsolvable. That being said, sometimes it just makes more sense to make the data easier to work with before bringing it into Tableau.

Note: This is an occasional series that provides answers to some of the most commonly-asked questions to Tableau Doctor.

Calculating Time Between Dates in the Same Column

A common problem we sometimes run into involves calculating the time between activities. Tableau is great at calculating the time between two dates if they are in different columns. You can use this simple calculation, for example: DATEDIFF( day, [start date], [end date] ).

But what about dates that are in the same column? Tables with order data or opportunity stage history often store dates in a single column:

How do we calculate the time between orders? How do we calculate the time that it takes to get from one stage to another? The Tableau solution is to use a LOOKUP function:

Don’t get me wrong—this is a quick and easy way to solve the problem. Where this tends to become less ideal is when the data sets become bigger. Remember that Table Calculations such as LOOKUP run in-memory and can hamper performance when used with larger data sets. Also, we might want to view the data differently. For example, maybe we just want to know the average time between orders as a bar chart per region or just a simple table.

My proposed solution is a little bit of data manipulation using the custom SQL dialog in Tableau. Thinking about the problem logically, we just need to shift our entire date set by one row to align the dates with the following or previous date. This would allow for side-by-side comparison of consecutive dates.

The Solution

Let’s walk through the steps. Connect to the data and drag in the orders table to the connection screen. From the data tab in the tool bar, choose “Convert to Custom SQL.” Use a DENSE_RANK() command to rank order dates (ORDER BY), and restart for every customer name (PARTITION BY). Be sure to add the comma highlighted in red anytime you add another column to the SELECT statement.

This produces a table with each date numbered from the first order date to the last order date (1 through N) for each customer. Copy this custom SQL, and drag a “New Custom SQL” table into the table connection view to be joined with the one above. Paste the above SQL in this dialog. Change the end of the DENSE_RANK command to “+1” and name this field “Previous Order Number.” The “+1” will shift the data set down by one row so we can align pervious order dates with the next order dates. You may also want to rename the order date in this command to “AS [Previous Order Date].”

Join on “Order Number = Previous Order Number” and “Customer Name = Customer Name.”

Connect to the data, and now the time between orders is an easy row-level calculation: DATEDIFF( day, [Previous Order Date], [Order Date] ). Now that the data is shaped for analysis, we can ask all kinds of questions such as how long, on average, it takes a customer to make a second purchase. Note that there will be no data for Order Number 1 since it’s the first order and therefore has no previous date to compare it to.

Advanced Scenario: Calculating the Length of the Opportunity Stage

Sometimes your data might be more complex. What if you have multiple rows of data logged for an individual stage of an opportunity? We don’t want to know the time it took to get from one commit point to another commit point; we want to know how long it took to get from the first commit point to closed won.

Follow the steps from the first example to find the time between stages. Create a second Custom SQL table and copy the code from above. Add "1" to the “Stage Number” to offset it so that it can be joined back to the original table. Join the tables on the “OpportunityId” and the “Stage Number.”