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.

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

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.

More Tips and Tricks from Tableau Doctor

También podría interesarle...

Comentarios

Submitted by Evan (no verificado) on 13 Octubre, 2015 - 17:26

Hi Bronson. I am interested in doing this and it would be very useful, but I am having a hard time following your example.

When you say, "Connect to the data," what data do you mean? I found a 2012 Superstore Excel file that matches some (but not all) of your images... was part of this example done with old data and part of it with a newer Superstore file?

Later, you say, "From the data tab in the tool bar, choose “Convert to Custom SQL.”" Where is the data tab and where is the 'Convert to Custom SQL' command? Do you need to be connected to a MySQL database for that to be an option? Did you mean "New Custom SQL?"

Hi Evan -
Good to meet you today at TC 15. Sounds like you figured out that converting to custom SQL is only available with a SQL database connection (or with excel using the legacy connection). Further you need a database that supports DENSE_RANK().

Feel free to describe the challenge you were having today in more detail and I can see what the possible solutions might be.

Hi,
I have a column which has three values, supposedly A,B and C.. Now my task is to find the value of Sum(A)-(Sum(B)+Sum(C)).
This is like finding the difference between the values which are in the same column.
Can I get some urgent help please..

hai,i'm razzmie,how can i set a fixed time in one date and another fixed time in another date,example January 24, i want to display the time from 11 am to 24 am and for 25 January i want to display as 1 am to 11 am,if possible, can u teach me how, i can send you the workbook package , or u can contact me in this email address: mie_ni92@yahoo.com