I love working with dates in Tableau’s products. After spending hours of my life coding in SQL different date parts or using reference tables to find out if a customer came into a bank branch on a weekday, it’s refreshing to be able to complete this task in just a couple of clicks. However, sometimes you need to go beyond those click-only options.

As a quick reminder of Date data fields, you can always check out this How to… post on dates.

Desktop Recap

In Tableau Desktop, you have a lot of different levels of a single date field to work with. By dragging and dropping a date field from your data pane and holding down the right mouse button in Windows (or holding down the Option key on a Mac), you will see lots of the different slices of a date available to you:

The different sections in the figure above demonstrate the different ways that you might want to use a date field:

1st section - allows you to make the date either continuous or discrete. Quick reminder, a continuous data field creates a single axis when placed on the Columns or Rows shelf and a discrete field creates an individual header for each of the different values in that field.
2nd section - date parts - this is where a date is split into its separate parts by just returning the day value, week number, or year. You’ll also spot that Tableau applies its own calendar to these date values to show what day of the week that date fell on or what quarter it is in.
3rd section - date aggregations - this allows you to count how many times a date is found in your data set or the earliest (min) or latest date (max).
4th section - date values - this is where some of the detail is removed. For example, you could ignore which day something happens so it can get ‘rounded’ up to the month level instead.
5th section - ATTR - the attribute function in Tableau checks to see if the minimum value is the same as the maximum value. When using attributes with dates, Tableau checks if the earliest date in a cell is the same as the latest date, if so it will return the date, otherwise it will return an asterisk.

With so much flexibility with dates in Desktop, why might you want to manipulate dates in Prep Builder? Simply to make the analysis faster and easier for users who might not know how to take advantage of all the different date options available.

Prep Builder click-only options

As a reminder in Tableau Prep Builder, you have a number of options when working with dates that only require a few clicks. When clicking on the ellipsis menu on a date field, you will find many options to just return part of a date without having to write any calculations at all.

When you use these options, Tableau Prep Builder will replace your date with the date part selected so you might want to duplicate your date field first if you want to keep the date for use later.

Date Functions in a calculation

When preparing data for analysis, you sometimes will need more than just parts of dates to answer your questions. For example:

Do our products ship out to customers within their Service Level Agreement?
How long does it take for an Order to Ship out?
- Does that differ per Region?
- Does that differ per Product Category?
How many customers place an order per month?

The list could be endless as each industry and department you might work in will have their own challenges. In this post, I will use Superstore, a sample data set in Tableau to allow you to follow along if you want to.

Let’s go through the functions in turn for those that can’t be handled with single click functionality in Prep Builder and have a look at some of the examples of how you might want to use them:

DateAdd

Dateadd() is used to add an increment to a date. This function is useful as if you just add +1 to a date it will just add one day. When working with dates, you won’t always want to work at the day level and add a month instead. As months contain different numbers of dates, this can be challenging. Dateadd() lets you choose the increment you want to use whether it is day, week, month, quarter, year.

Example: you want to add a week on to each Order Date to know when you need to Ship the order by:

The result of this calculation is a datetime data type with the increment made:

This date could be the target your operational team then needs to meet and may help in prioritising the work.

The increments in the dateadd() doesn’t have to be positive.

Example: You want to know when the customer actually made the order if you know your systems take a couple of days to process.

Again, the output of this calculation is a datetime data type:

DateDiff

Whilst dateadd() adds an increment to a single date, datediff() compares the difference between two dates. Again, you can specify how you would like to measure the difference, whether in days, weeks, months, quarters or years.

Example: Did we ship the order by the due date we just created? If so, how much by?

The output of this field isn’t a date but an integer showing the number of days between the first date in the calculation and the second date.

Here we can see see that we have no negative values so everything was shipped out on time. Maybe we are being too kind to our operations teams and am giving them too long to ship the products?

DateName

Like datediff(), datename doesn’t actually return a date data type. Instead the function returns the name or word describing the date part requested. This differs from the Convert Date described above as the name of the date element is returned instead of the number.

Example: You want to return the month name as in a different data set, this is how the date is shown and you want to join the data sets on the month name.

Here’s what the output looks like:

DateParse

One of the main reasons to work with data before analysing it is to clean up the data. Dateparse() allows you to take a string value and convert it into a date. You might be tempted to convert a string to a date just by changing the data type by clicking on the data type icon at the top of each data field in the Preview Pane. This may work but frequently you will find the data is too messy for this technique to work.

Example: You’ve been asked to analyse a system extract but the date field doesn’t read into Tableau as a date and you will need to analyse when something happens in your analysis.

Dateparse() allows you to specify how Tableau should read the date format. The date field symbols are shown in this Tableau post under the Date Field Symbols section.

Like some of the earlier functions, this function outputs as a datetime data field.

DateTrunc

Datetrunc() removes some of the detail of a date or datetime field to a less granular level. Like many of the other date functions you can specify whether you want the date to move to the year, quarter, month etc level. This is a way to group dates together to answer your questions more easily.

Example: I want to analyse all my sales by week.

The output of this calculation is still a datetime data type but this time the values shown are only in week increments as we can see in the example below. Both the 11th and 14th fall in the same week hence the identical value in the Week field.

IsDate

Unless you spend time viewing your incoming data set, you might want to make use of some functions to check the validity of the data to meet certain data types. This can help with automated feeds of data to ensure the data is coming into the data set correctly formatted for how you want to use it.

Example: Identify if a field that needs to be a date for analysis

The output of this function is a boolean field (true or false). If the field being tested is a a date, the calculation will return true, otherwise false if the format doesn’t fit a date data type. This can allow you to filter out the falses or branch them off for a separate flow to clean the data up (maybe with a dateparse() function)

Today / Now

The today() or now() function returns either a datestamp or date timestamp of your current system’s time. This is really useful if you need to record in the data set when the data was processed by Tableau Prep Builder.

Example: You want to add the date of when the Prep flow is processed to show in your reporting.

This function outputs a constant date.

This date doesn’t have to just be used in this way as it can be used in more complex calculations to determine the difference between a date in the dataset and today’s date.

Summary

Date functions are hugely flexible and powerful. Practising them will help you use the different functions more efficiently and effectively when you need to. By knowing you options, you will be able to set up your data set in the best way to meet your users analytical needs without them having to write the calculations themselves.

How to… use date functions