How to… create date parts
The development team for Tableau Prep are always looking to make your common data preparation tasks easier. Working with dates are a common task for most people. This kind of work involves:
Transforming string fields to date fields
Cleaning date fields of incorrect entries (often when converting from string fields)
Picking apart date fields to just return a specific part of a date
The focus of this post is going to be the additional options we now have in the Clean step to pick apart the dates with some simple options. The examples shown in this post are from Preppin’ Data 2021’s week one challenge.
The options
When you are connected to a data source that has a date field within it, the first thing to do is check that Prep Builder is reading the field as a date. On the Input step, ensure you are seeing either the calendar icon (see below) or the calendar with a clock in the bottom right hand corner to indicate a date time field.
Understandably, there is no option to pick out data parts from a string or numerical field. The option is not even listed.
When you have a date field, you can find the option to create the date parts under the ellipsis menu in the profile pane for the Date data field:
When you select the Convert Dates option, you will be presented with a few options:
Date and Time - converts the Date to a Date Time field. If there is no time detail, the time will be recorded as midnight 00:00:00
Year Number - just the year of the date ie. 1990, 2020, 2021
Quarter Number - the quarter based on the standard Jan to Dec year ie any date in Jan through to Mar inclusive will return 1.
Month Number - will return 1 (Jan) through to 12 (Dec)
Week Number - will return 1 to 53 with weeks calculated Sunday to Saturday
Day of Month - will return 1 to 31 day of the month
Customer Financial Year - this is covered in this How To… post
Quite simply this small menu let’s you get to many of the elements of a date where you might have previously written DATEPART() calculations like this example to extract quarter from the Date:
These calculations have always been tricky for new users of Tableau as you need to write the date part (ie where we are entering quarter) in:
lowercase
singular (ie no ‘s’ on the end of your date part)
in quote or speech marks
This might sound simple but always took a little bit of getting used to for people who haven’t worked with other data tools before.
Time to pull apart the dates?
Not quite yet, when you select any of the ‘Convert Dates’ options you actually overwrite the existing Date field. This might be fine in a few use cases but I’ve found I want to still have the original field to:
Check the output of the change I’ve made and has it produced the desired result
Complete additional conversions or calculations to the original field
Therefore, I fully recommend you duplicate the original Date field before you pick any options.
Now can I get what I what?
Yes you can! By duplicating the field, you can pick as many date parts as you want. Just remember to duplicate the field each time you want to pick another date part.
Here’s an example where I have duplicated the Date field twice to be able to form ‘Quarter’ and ‘Day of Month’:
In your Changes pane in Prep Builder, any of the Convert Date options are added as calculations that you can edit if you want to. These are great for newer users of Prep Builder to learn how to write these calculations from scratch themselves. Remember if you write the calculation yourself, Prep Builder will create a new field and will therefore not need to duplicate the original field.
Resulting calculation created by Prep Builder for Day of Month
Overall, Convert Dates will save you time and allow you to make use of different parts of dates in your analysis. I do hope to see some of the other options currently available in Desktop available translate into Prep too. For example ‘day of week’ would be a useful addition (pretty please Prep Developers).
