Understanding Tableau Date Functions: A Comprehensive Guide

Data Visualization tools are becoming more popular in the market day by day and their efficiency to gain customer insights is always being put to the test. These tools help organizations understand their customer needs and help them survive in a competitive market. One such BI (Business Intelligence) tool that helps you analyze your data regularly to gain customer insights in Tableau. Tableau Data Functions provide interesting ways to perform analysis of your data in Tableau.

Tableau Date Functions are an in-built feature of Tableau which allows you to perform a variety of Date operations on multiple data records. It also allows you to gather insights from multiple sources of data with respect to the date.

This article provides a comprehensive guide on Tableau Date Functions and explains how you can implement them in your organization. It also gives you a brief overview of Tableau and some important code snippets to implement Tableau Date Functions easily. Read along to find out about Tableau Date Functions and how they can prove to be an asset to your organization.

Table of Contents

• What is Tableau?
• What are Tableau Date Functions?
• Use case of Date Functions in Tableau
• How to set the default Date format in Tableau?
• Properties of Tableau Date Functions
  • Default Calendar
  • Week Start
  • Fiscal Year Start
  • Date Format
• Method to Convert Field to Date Data Type
• Popular Tableau Date Functions
  • DATEDD
  • DATEDIFF
  • DATENAME
  • DATEPARSE
  • DATEPART
  • DATETRUNC
  • DAY
  • ISDATE
  • MAKEDATE
  • MAKEDATETIME
  • MAKETIME
  • MAX
  • MIN
  • MONTH
  • NOW
  • QUARTER
  • TODAY
  • WEEK
  • YEAR
• Steps to Create Date Calculations in Tableau
• Creating Custom Dates in Tableau
• Converting Fields to Date Fields using the DATEPARSE Function
• Conclusion

What is Tableau?

Tableau is a Business Intelligence (BI) tool, that lets users analyze data, create visualizations, and develop dashboards and reports. Tableau is available in two flavors – A completely managed online service called Tableau online and an on-premise installation called Tableau Desktop. Off late Tableau is also offering a suite of applications to make it easier for analysts to connect to data, prepare data, and manage the report generation jobs.

The effectiveness of a business analyst lies in his ability to portray the data used for decision-making in the most intuitive way. Tableau’s biggest strength is its ability to generate valuable insights from multiple sources of data by using features such as Tableau Date Functions.

Key Features of Tableau

Tableau has a wide range of features which makes it a better choice over other BI tools. Some of these are as follows:

• Tableau provides extensive features in Dashboard to perform analytical analysis on the data and allows users to create a visual masterpiece. 
• With Tableau, users can collaborate sheets among colleagues and other team members to review the designs.
• Tableau supports real-time data as well as batch data with robust in-memory computation.
• Tableau has over 200+ connectors available in its library, which can connect to any relational and non-relational databases, CSV files, Excel, Hive, Snowflake, etc.
• Tableau allows users to develop advanced charts and graphs to create high-quality visuals.

To learn more about Tableau, click this link.

What are Tableau Date Functions?

The Date function is one of the available Functions found in Tableau. It is an inbuilt function that allows you to manipulate data records in a data source and gives you the power to create basic date operations on records as well as create complex date expressions.

With the constant bombardment or generation of large volumes of data these days, dealing with Time-Series data can be a challenge in Business Intelligence if you do not know how to handle them effectively. Choosing the appropriate method to create a data view, converting fields to a date field, identifying if a data is a date, are some of the very important properties you will encounter when dealing with date and time-related variables and not having date values like year and months in a data source would make it difficult to come up with a creditable data analysis because of the lack of a timestamp on them.

For example, you may be given yearly time series data of a sales campaign but might be required to carry out a quarterly or monthly analysis on them to determine profits margins, so having the right knowledge to manipulate such data becomes valuable. With the help of Tableau Date Functions, you can apply your skill to create logical or arithmetic operations on the date values as required by the analysis.

Use Case of Date Functions in Tableau

Date functions in tableau ease you to apply arithmetic calculations on date values to perform analysis. With the help of Date functions, you can easily convert strings, and numbers to date data types.
Date functions assist you in manipulating dates in your data source. Using Date in your analysis process gives you the reference of time such as weekly, monthly, yearly, etc. Tableau has various useful date functions which help in creating date fields as well as operating complex queries on date fields.

How to set the Default Date Format in Tableau?

You can easily set the default Date format in Tableau with few settings.
Click on the Date field option in the dimension list. Set the default date format by setting Default Properties as shown in the image given below.

After setting the default format, use this field with the exact date – discrete setting to display the default format.

Tableau Default Date format doesn’t end here, you can even customize the default date format in tableau. Tableau provides a complete list of customizable date format options.
A custom date format of mmm-yy looks like as depicted in the image below.

Properties of Tableau Date Functions

Tableau Date Functions allow you to manipulate dates in your data source by changing the date fields from the year, month, and day to new date values. To fully understand how to configure these variables, you need to understand the date properties and how they can be used.

The options found in the date properties dialog box are:

• Default Calendar
• Week Start
• Fiscal Year Start
• Date Format

1) Default Calendar

This Tableau Date Function property specifies the calendar system available for use and you can choose between the Standard Gregorian or ISO 8601 Week-Based systems. The ISO 8601 format is an international standard for calculating date and time and is different from the Gregorian calendar as the starting week of the year which is week 1 differs in both. Also, in the Gregorian calendar you can decide on which day the week begins but in the ISO 8601 standard, the week always commences on Monday.

In the ISO 8601 format since a new week starts on Monday, if January 1st falls on a Friday, then week 1 will not begin until the following Monday, January 3rd while in the Gregorian calendar, week 1 of the new year is counted as starting on January 1st regardless of the day of the week and in this example on Friday, making week 1 to have two days in it that is Friday and Saturday and week 2 will begin on Sunday.

You can freely switch between them but setting the default calendar goes a long way to determining how dates are used.

2) Week Start

This Tableau Date Function property is used to specify which day should be used as the first day of the week since the day considered as the first day of the week varies from location to location. In some regions, Sunday is regarded as the first day of the week in certain regions while Monday is seen as the first day of the week in other regions.

3) Fiscal Year Start

This Tableau Date Function property allows you to state which month you deem as the first month of your fiscal year as January may not be considered as the start of your fiscal year.

In some scenarios, an organization’s fiscal year might not match the traditional calendar year which starts on January 1st and this has to be reflected for analysis. The organization’s fiscal year might begin on March 1st through to February 31st, in such a case, this has to be displayed on the dashboard so you can also determine the company’s fiscal quarter and fiscal week as it would be different from the calendar equivalent.

4) Date Format

This Tableau Date Property is a default format that specifies data dimensions.

To set the date properties for a data source be it Calendar, Week Start, Fiscal year, or Date Format, right-click your data source in the Data pane and choose Date properties as shown below.

Method to convert Field to Date Data Type

Generally, there are two methods to convert the field to date data type.
1. Changing the Data Type of field without using the Date functions.
2. Changing the Data Type of the field by using Date functions.

Changing the Data Type of field to Date Data Type without using the Date function

You can change the data type by right-clicking on a field in the Dimension List and Change Data Type. This does not always work. When a field becomes NULL, Tableau does not recognize it as a date.

Changing the Data Type of field to Date Data Type using the Date function

You can use the date conversion formula present in Tableau, such as DATEPARSE, DATE, or MAKEDATE to convert data type of field to Date data type.

Popular Tableau Date Functions

There are numerous Tableau Date Functions. Below is a list with their specific allocated syntax, descriptions, and examples to help you choose a preferred function as required by your analysis.

Many date functions use date_part which is a constant string argument and it includes the year, quarter, month, and others.

1. DATEDD

This Tableau Date Function returns a specified date which is gotten by adding an interval to the original date. The syntax and examples are given below.

Syntax:

DATEDD(date_part, interval, date)

Example: To add 4 months to the present date, simply type DATEDD(‘month’, 4, #2021-04-20#) and you will get 2021-08-20 as four months have been added to the original date.

2. DATEDIFF

This Tableau Date Function returns the difference between two dates set as date 1 and date 2. The syntax and examples are given below.

Syntax:

DATEDIFF(date_part, date1, date2, [start_of_week])

Example:

DATEDIFF(‘week’, #2021-04-18#, #2021-04-20#, ‘monday’)= 1

DATEDIFF(‘week’, #2021-04-18#, #2021-04-20#, ‘sunday’)= 0.

In the first example, 1 is returned as the start of the week is set as Monday hence the 18th which is a Sunday falls on a different week from 20th which is a Tuesday. The second example returns 0 as the first day of the week is set as Sunday and the 18th which is a Sunday is in the same week as 20th, a Tuesday hence 0 is returned.

Syntax:

DATENAME(date_part, date, [start_of_week])

Example:

DATENAME(‘year’, #2020-04-15#) = ‘2020’ , DATENAME(‘month’, #2020-04-15#) = ‘April’

The first example returns ‘2020’ as the specified option is ‘year’ and the second example returns ‘April’ as the specified parameter is ‘month’.

4. DATEPARSE

This Tableau Date Function returns the date_string as a date. It is used to convert a field to date. The date_format argument describes how the string field is arranged. The syntax and examples are given below.

Syntax:

DATEPARSE(date_format, [date_string])

Example:

DATEPARSE(‘yyyy-MM-dd’, #2021-04-20#) = ‘April 20, 2021’

5. DATEPART

This Tableau Date Function returns the value entered in the date_part parameter as an integer value. The syntax and examples are given below.

Syntax:

DATEPART(date_part, date, [start_of_week])

Example:

DATENAME(‘year’, #2020-04-15#) = 2020 , DATENAME(‘month’, #2020-04-15#) = 4

6. DATETRUNC

This Tableau Date Function is used to perform a truncate operation on a specified date to the accuracy specified on the date_part. When truncated, the function returns a new date. The syntax and examples are given below.

Syntax:

DATETRUNC(date_part, date, [start_of_week])

Example:

DATETRUNC( ‘month’, #2020-08-22#) = 2020-08-01 12:00:00 AM , DATETRUNC( ‘quater’, #2020-05-22#) = 2020-04-01 12:00:00 AM.

In the examples above, the date is truncated in the middle of the quarter, and the middle of the month returns the first day of the quarter and the first day of the month respectively.

7. DAY

This Tableau Date Function returns the day of a given date as an integer. The syntax and examples are given below.

Syntax:

DAY(date)

Example:

DAY(#2021-02-20) = 20

8. ISDATE

This Tableau Date Function is a condition function that returns true if a given string or value is a valid date. The syntax and examples are given below.

Syntax:

ISDATE(string)

Example:

ISDATE(‘April’ 20 2021) = true

9.MAKEDATE

This Tableau Date Function returns a date value constructed from the specified year, month, and date. The syntax and examples are given below.

Syntax:

MAKEDATE(year, month, day)

Example:

MAKEDATE(2021, 4, 20) = #April 20, 2020#

10. MAKEDATETIME

This Tableau Date Function returns a DateTime that combines date and time. The date part can be a date, DateTime, or a string type but the time part must be a DateTime type. The syntax and examples are given below.

Syntax:

 MAKEDATETIME(date, time)

Example:

MAKEDATETIME(‘1986-01-22”, #07:59:00#) = #01/22/1986 7:59:00

11. MAKETIME

This Tableau Date Function creates a date constructed from the hour, minute, and second. The syntax and examples are given below.

Syntax:

MAKETIME(hour, minute, second)

Example:

MAKETIME(14, 42, 52) = #14:42:52#

12. MAX

This Tableau Date Function returns the bigger value of dates a and b when compared and the dates must be of the same type. It returns Null if either argument is Null. The syntax and examples are given below.

Syntax:

 MAX(expression) or MAX(expr1,expr2)

Example:

MAX(#2021-01-01#, #2021-03-01#) = 2004-03-01 , MAX([ShipDate1], [ShipDate2])

13. MIN

This Tableau Date Function returns the smaller value of dates a and b when compared and the dates must be of the same type. It returns Null if either argument is Null. The syntax and examples are given below.

Syntax:

 MIN(expression) or MIN(expr1,expr2)

Example:

MIN(#2021-01-01#, 2021-03-01#) = 2021-01-01 , MIN([ShipDate1], [ShipDate2])

14. MONTH

This Tableau Date Function returns the month of the given date as an integer. The syntax and examples are given below.

Syntax:

 MONTH(date)

Example:

MONTH(#2021-04-20#) = 4

15. NOW

This Tableau Date Function returns the current local system date and time. The syntax and examples are given below.

Syntax:

NOW()

Example:

NOW( ) = 2021-04-20 1:08:02 AM

16. QUARTER

This Tableau Date Function returns the quarter of the given date as an integer. The syntax and examples are given below.

Syntax:

QUARTER( )

Example:

QUARTER(#2021-04-20#) = 2

17. TODAY

This Tableau Date Function is used to get the current date. The syntax and examples are given below.

Syntax:

TODAY()

Example:

TODAY( ) = 2021-04-20

18. WEEK

This Tableau Date Function returns the week of the given date as an integer. The syntax and examples are given below.

Syntax:

WEEK( )

Example:

 WEEK(#2021-04-20#) = 17

19. YEAR

This Tableau Date Function returns the year of the given date as an integer. The syntax and examples are given below.

Syntax:

YEAR(date)

Example:

YEAR(#2021-04-20#) = 2021

Steps to Create Date Calculations in Tableau

The following steps below can be used in creating a date calculation in Tableau:

1. In Tableau Desktop, connect to a data source.
2. Go to an active worksheet
3. From the Data pane, under Dimensions, drag and add your fields in Rows and Columns.
4. Select Analysis and then click on the Create Calculated Field option.
5. A calculation editor opens and you will input a Name for your calculated field e.g Date of Purchase.
6. Enter the date function formula you require like DATETRUNC(‘quarter’, [Date of Purchase]).
7. Click OK when you are through and the new calculated date field will appear with the name you gave it in the Dimensions section of the Data pane.
8. You can now proceed to manipulate the new date field in your charts by dragging and dropping it on the Rows or Columns section to get Exact Date, Discrete or Continuous, etc depending on the type of analysis you want to carry out.

An example of a date calculation in Tableau is shown below.

Creating Custom Dates in Tableau

You can create a custom date for yourself especially when it is used repeatedly. The custom date is created when working in the Data pane and it becomes a new field in the Data pane. To do this, carry out the following steps:

1. Right-click a date field in the Data pane and then select Create.
2. Click Create Custom Date.
3. In the Create Custom  Date dialog box, type a name to be given to the custom date.
4. From the Detail option, select the level you want the date to display.
5. Specify if you want the custom date to be discrete (Date Parts) or continuous (Date Value).
6. Click on OK

Converting Fields to Date Fields using the DATEPARSE Function

Databases store Date fields in their unique way and this might become difficult for other applications to interpret them as they may be imported as a text string instead of a numerical field, thereby, making it difficult to be used for analysis.

To change the data type of the field using Tableau Date Functions, go to the Data Source or Data pane, click the data type icon, and change the data type to Date or Date & Time. If the data type does not convert as needed but displays Null values, you will need to create a calculation using the DATEPARSE function.

To do that, follow the given steps:

1. Inspect your date field to note where years, hours, and days are displayed in your field.
2. Right-click on the date field in the Dimension pane and select Create.
3. Click on Calculated Field.
4. In the dialog box that comes up, write the DATEPARSE function. It consists of two parts: a format that Tableau uses to interpret the string as date and string which is the field you wish to convert and must be a string type.
5. Confirm your format against the string displayed in Tableau, then click OK.

Conclusion

This article has deeply explained the concept of Tableau Date functions, analyzing its properties, detailing the available date functions, showing you how to create a custom date function, and convert a field to a date field.

It covered all of these concepts to give you a better understanding of the Tableau Date Functions so you can carry out an efficient date calculation when performing your data analysis. Performing such an analysis can be the difference between having quality insights and poor deductions which will lead to half-baked decisions.

In case you want to integrate data into your desired Database/destination and seamlessly visualize it in a BI tool of your choice such as Tableau, then Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and the data destinations.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of Understanding Tableau Date Functions in the comments section below!