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

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

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.

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.

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.

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.”

Now that the data is shaped properly, you can easily calculate the time between stages in Tableau! Here's the full workbook for your reference.

Got a question you’d like Tableau Doctor to address in an upcoming blog? Email your ideas to ideas@tableau.com.

Use an LOD Expression to Conquer Database Cardinality Issues
Map Your Database for Easier Navigation