Sign in or start a free trial to avail of this feature.
6. Introduction to Tableau Date Functions
In this lesson, we learn how to use some of Tableau's most popular date functions, DATEPART(), DATETRUNC() and DATEDIFF.
To explore more Kubicle data literacy subjects, please refer to our full library.
Dates in Tableau
- Dates have their own set of functions in Tableau
- However, most of the time, you will only use two functions DATEPART() and DATETRUNC()
- These functions are normally accessed through the pill dropdown, rather than typed
How DATEPART() works
- Isolates a particular part of a date (e.g. month) and returns that value
- Can be used to calculate all units sold in a calendar month
- However, if you have mulitple years of data, it will combine the March sales for all years
How DATETRUNC() works
- Converts a date back to the beginning of a specific time period (e.g. quarter)
- For example, if a row of data contained 28/2/2015, this would be converted to 1/1/2015
- Works for all different types of units (year, quarter, month, week etc.)
How DATEDIFF() works
- Accepts a unit (e.g. quarter) and two dates as arguments
- Calculates the number of units between two dates
- Output value depends on both the dates and the units accepted by the function
Tableau provides you with many different date functions. Most of which we tend to use from the pill drop-down in the shelf. When I hit the drop-down option, I can see lots of different date value options from year, month, to week num and weekday. Each of these options represents a date function applied to the date column. Let's say, I double click on month and then select this pill, so I can view the formula. And this particular option uses the DATEPART function that returns a numeric value indicating the part of the date field specified by month.
So for example, all of the sales that contributes to this particular point, has a DATEPART month of three which corresponds to March. With DATEPART, because it's simply analyzes the month element of your date, it doesn't actually distinguish between sales in 2012 or 2013. Instead, adds all the March sales together, regardless of year. While this may be of benefit the auto occasion, it's much more likely you'll want to see March data for 2012, 2013 and 2014 separately. And to do this, we use a different date function called DATETRUNC.
So let's hit the drop down and this time I'll select month, but in the second part of the menu, you can see that a year is included, which means in my visualization, May 2012 is different to May 2013.
Let's now look at this formula. It's in the exact same format as before where month is specified as the unit, I use the same date column, but the function is called DATETRUNC, instead of DATEPART. DATETRUNC is simply a rounding function that takes a particular date and runs it to a particular date for the correct month or quarter, depending on the unit you specify. So for example, if I had a sale on the 23rd of January, DATETRUNC accepting month would round this back to the 1st of January. If DATETRUNC was accepting quarters, then a sale on the 1st of August would be truncated back to the 1st of July, representing the start of Q3. And this way, DATETRUNC tends to be much more valuable than DATEPART. For both DATEPART and DATETRUNC, a string of text from one of the following options needs to be passed to the function. Typically I tend to use quarter and month probably the most, but this depends entirely on your data set.
While these two functions are by far the most popular date functions, it's worth exploring some of the additional functions on offer as well. I'm going to start with DATEDIF. This function accepts a string such as month or day and two dates, returning the distance in the unit specified between the two dates. In our current dataset, we could use this functionality to track how often certain customers are ordering specific products which will help us predict inventory levels. To start, I'm going to create a new sheet and I'm also going to create a new calculated field and I'll call it months between consecutive sales.
And I'll start by writing a DATEDIFF function.
And the first argument is going to be month.
And the second argument is going to be the start date. Now the DATEDIFF function needs to accept aggregate data. I'm going to wrap the attribute field around the date function.
And then the second argument is going to be a lookup based on the date function as well.
I'll write a look up that will take the attribute of date and that will also accept a number, which would be my offset, which is equal to one.
This formula is quite complex. So let's step through it, one term at a time.
I start with the DATEDIFF function which returns the time between the start date and the end date. The DATEDIFF function can return time in a number of different units, but here I'll specify months. So the function will now return the number of months between the two dates entered. For my start date, I'll simply use the date field, which is the date that a customer makes a purchase. And for the end date, I will use a lookup function to find the date of the next purchase, by creating an offset of one. If you need to recap on the lookup function, I'll link to this lesson in our show notes.
Now my calculation is valid, so I'll press okay.
I'll put the date column in rows and I'll also create a text table for this data.
And then I'll simply add my calculated field as a text option.
What this is currently telling me is that for my full sales data set there's one month gap between consecutive sales, which isn't surprising considering that I have monthly data.
So now I need to add some filters. I'll filter product name, I'll just select Vitalume, and press okay.
And then separately I'll filter on customer ID, I only want to isolate three customers of interest.
And as you can see my data now changes.
Between January and February, I make a sale, but then I do not make another sale for three months until May. And then again until August, but then again a sale in September. And using date difference, I can begin to understand how often these customers will order from me.
A worrying insight is that in 2014 Vitalume had no orders between July and February for these three customers. And the company might want to explore why this is the case. In the next lesson, I'm going to show you some additional helpful date functions such as the today function.