Working with Dates is an essential part of any analysis. However, it can also be tricky and messy at times depending how dates are stored in the databases and what manipulation we need to do on them. Typical analysis on Dates is to find out Sales trends over a period of time or what the year over year growth has been. For a customer centric organization, Date calculations will be useful for understanding the customer's buying patterns. Taking this point ahead, let us do some calculations to find out the First purchase date and the Last purchase date of customers. We will then use the Last purchase date to find the out how long it has been since the customers last purchased from us.
Let us see how this can be done in the following recipe.
For this recipe, we will work on the Order Date field and the Customer Name field from the Orders sheet of Sample - Superstore.xlsx
data. We will continue working in our existing workbook. Let's get started with the recipe.
DATEDIFF
function in Tableau. The formula is DATEDIFF('day', [Last purchase date], #2014-12-31#).In the preceding recipe, we created two calculations: the First purchase date and the Last purchase date. We created both the calculations from scratch by using the Create Calculated Field… option on the Order Date field. However, as an alternate approach, we could have only created the First purchase date calculations from scratch by using the Create Calculated Field… option on the Order Date field and then duplicated and modified the same to get the Last purchase date. In order to use the alternate approach of duplicating and then modifying the existing calculation, all we had to do was to right click on the First purchase date field and then select the Duplicate option, which would result in a new calculated field called First purchase date (copy). We could then simply right click on this copy field and select the Edit… option and change the name from First purchase date (copy) to Last purchase date and modify the Formula from MIN([Order Date]) to MAX ([Order Date]).
After finding the First purchase date and the Last purchase date, we then created another calculation called Days since last purchase by using the DATEDIFF
function in Tableau. This DATEDIFF
function helps us specify the date part, which essentially helps us compute the difference in two dates in terms of years, months, days, and so on. In the preceding recipe, we fixed our end date as December 31, 2014. Thus, in the preceding recipe, we can see that it has been 1,166 days since Nicole Brennan has last purchased from us as of December 31, 2014.
Currently, we are seeing a long list of all the customers. However, if we want to narrow our focus on let's say only the customers whose last purchase date with us has been more than 30 days or more than 60 days or maybe even more than 365 days as of 31st December 31, 2014, then we can use the filter control to get a list of only the relevant customers.
Tableau offers a wide variety of Date functions. To understand more about these, refer to http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_date.html.