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.
- Let us create a new sheet by pressing Ctrl + M and rename it Date Calculation.
- We will start by dragging Customer Name from the Dimensions pane and dropping it into the Rows shelf. Next, we will right click and drag the Order Date field from the Dimensions pane and drop it into the Rows shelf, just right after the Customer Name. Let us make sure to select the MDY(Order Date):
- This action will give us the actual dates on which the Customer has placed an order with us. However, instead of looking at all the transaction dates of a Customer, it would be great if we can quickly find the First purchase date and the Last purchase date of these customers. Now, to do so, let us right click on the Order Date field in the Dimensions pane and select the option of Create Calculated Field…. Let us name this calculation as First purchase date.
- First purchase date is the date when the customer made their first transaction and to find this First purchase date, we will take the Minimum of Order Date. Thus, our formula will be MIN([Order Date]).
- Similarly, the Last purchase date is the date when the customer made their last transaction and to find this Last purchase date, we will take the Maximum of Order Date. Thus, our formula will be MAX([Order Date]).
- Refer to the following screenshot:
- Now that we have both these calculations ready, let us use them in our view. We will find these calculations in the Measures shelf. Let us drag and drop the First purchase date and Last purchase date into the Rows shelf. Also, let us remove the MDY(Order Date) pill from the Rows shelf. Our view will be updated, as shown in the following screenshot:
- Now that we have the First purchase date and the Last purchase date of the customers, let us also find out how long it has been since the customers last purchased from us. Now, what we want is essentially the difference in days between the customer's last purchase date and today. However, the data that we are using has information only till December 31, 2014. So, if we take the difference between the last purchase date and today, we will get huge difference in days, which won't make sense. However, we can tweak the logic a little and try to find out how long it has been since the customers last purchased from us as of December 31, 2014 instead of today. To do so, let us create a new calculated field and name it Days since last purchase.
- We will use the
DATEDIFFfunction in Tableau. The formula is DATEDIFF('day', [Last purchase date], #2014-12-31#). - Refer to the following screenshot:
- After clicking OK, we will drag this new calculation from the Measures pane and drop it into the Columns shelf. The view will be updated, as shown in the following screenshot:
- Let us convert this chart to a bar chart by selecting the Bar option from the dropdown in the Marks card. Refer to the following screenshot:
- For the last and final step, let us sort the customers in descending order by Days since last purchase, using the shortcut option from the toolbar, and let us also enable the filter of Days since last purchase by clicking on the dropdown of Days since last purchase from the Column shelf and selecting the Show Filter option. Refer to the following screenshot:
- Once we do that, let us also quickly enable the labels on the bars by selecting the Show Mark Labels option in the toolbar. Refer to the following screenshot:
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.