Sign in or start a free trial to avail of this feature.
7. More Date Functions in Tableau
In this second lesson on dates in Tableau, we examine DATEADD(), TODAY() and a couple of other popular functions that will help you manipulation time-series data.
To explore more Kubicle data literacy subjects, please refer to our full library.
Using the DATEADD() function
- Enables you to add a certain number of date units to a specific date
- The unit (e.g. month) is set by the user, as is the number of units
- DATEADD() simply returns a date as its output value
- Typically used to create a new column, represented by a calculated field
Using the TODAY() function
- Simply returns today's date as a variable to be used in formulas
- Very useful for calculating the amount of time since a customer last purchased a product
- MAX() function used to find the latest date in a field for this functionality
In this lesson, we're going to explore some additional date functions, which may prove useful to you in Tableau. In the previous lesson, we used DATEDIFF to calculate the number of months between two dates. In contrast to DATEDIFF, we can also use DATEADD. Say on average it takes each of these customers 35 days to pay an invoice and we want to create a new column called Payment Date to reflect this statistic. I'll right click and Create Calculated Field called Payment date and in here, I'm going to use DATEADD. DATEADD first accepts the part of the date that you want to add on. I'm going to use days.
Next, it requires the interval, which is 35 in my case. And lastly, it wants to accept a date field, which is simply Date. Then I'll press OK.
And now I can add Payment date to my table and I'll just remove the previous value. By default, this appears in the YEAR format. When I hit the dropdown, I can change this to Day. And when I do this, I can see that there's a 35-day gap between the date and the payment date. The next function I want to show you is simply called TODAY. This function just returns the current date. It doesn't require any arguments but it can be quite useful when finding the difference in time between the last payment and the current date. Let's use this function to find the difference in time between the last day that we received payment from each of my customers. I'll start by creating a new sheet and I'll drag all my customers onto this sheet. Next, I'll create a calculated field, which will be called days since last payment.
And the formula will simply be DATEDIFF and the date_part will be day, the start_date will be the last payment date, which I an find using the max function.
And then the end_date will simply be TODAY. And then I'll press OK. Let's now add this to my chart.
And I'll sort. And as you can see, we have a couple of customers who have not bought for a very long period of time and then lots of other customers who bought approximately 179 days ago. Now, this chart is a little misleading because we don't have a lot of data for the end of 2016 and early 2017.
But it does show you how this function can work very effectively when you want to see which customers haven't paid you in quite a while.
While there are a number of other date functions that you can choose from in this dropdown list, the functions that I've covered in the past two lessons cover about 95% of what I use in Tableau regarding dates. The three remaining functions I'm going to show you are probably the easiest to remember and they are DAY, MONTH and YEAR. These functions all accept a date as an argument and then return a number value. So for the 1st of October 2016, the DAY function will return one, the MONTH function will return 10 and the YEAR function returns 2016. If you're performing some calculations using dates, these three functions may be valuable at some point. Outside of this, you should be well on your way to manipulating dates in any format that you want in Tableau.