Author avatar

Deepika Singh

Construct Date Calculations in Tableau

Deepika Singh

  • Apr 30, 2020
  • 6 Min read
  • 3,243 Views
  • Apr 30, 2020
  • 6 Min read
  • 3,243 Views
Data
Data Analytics
Business Intelligence
Tableau

Introduction

In business intelligence and visualization, dealing with time series data can be a daunting challenge if you don't know how to deal with date and time variables. Constructing and choosing the appropriate method to create the date view is important. For example, you may receive a yearly time series sales data and might be required to do analysis on quarterly or monthly level. This will require you to work with date or time variables. In this guide, you’ll learn how to construct date calculations in Tableau.

Date Fields in Tableau

In Tableau, the date fields are often of two types, a normal date field format (DD/MM/YY) or one that includes a timestamp (hrs:mins:sec) in addition to the date. Most of the date functions consist of date_part as an argument that includes parts such as year, quarter, month, day, week, etc.

Data

This guide will use the sample superstore data source available in the Tableau repository. The variables used are Order Date' andShip Date`. In the subsequent sections, you’ll work with different date functions.

DATEADD

The DATEADD function is used to add a specified part of a date with respect to the defined interval and date field. The syntax is DATEADD(date_part, interval, date). The first argument defines which part of the date should be added, e.g. month, quarter, etc. The second argument defines the interval by which the data part will be incremented with respect to the third argument, date. This will be clear in the example, but you’ll start by creating a calculated field. To do this, go to the Analysis tab and select Create Calculated Field.

Output:

Imgur

The next step is to write an expression for the function.

Output:

Imgur

The calculation above will increment today’s date by six months. This means that if today’s date is March 21, 2020, the output will be September 21, 2020. To see the output, drag the calculated field DateAdd into the Rows shelf and right click on it to select the Exact Date option, as shown below.

Output:

Imgur

Performing the steps above will generate the desired output.

Output:

Imgur

In the above output, you can see that the date is incremented to September 21, 2020.

DATEDIFF

The DATEDIFF function is used to find out the difference between two date expressions with respect to the defined date-part. The syntax is DATEDIFF(‘day’, Date 1, Date 2), where Date 1 and Date 2 are the arguments to be used to find the difference for the date-part day.

Repeat the step of creating a calculated field discussed earlier, and write an expression for DATEDIFF as shown below.

Output:

Imgur

The next step is to drag the variables Order Date, Ship Date, and the new calculated field DateDIff into the Rows shelf. Now, you’ll convert these variables into exact date. This will create the output below where the DateDiff variable returns the difference between the Order Date and Ship Date.

Output:

Imgur

Note that the variable DATEDIFF returns the difference in days, hence it will be found in the Measures section and not in Dimensions.

DATENAME

The DATENAME function is used to fetch the date-part from the date expression. The syntax is DATENAME(‘date_part’, date). To implement this function in Tableau, create a calculated field, Date Name, using the formula shown below.

Output:

Imgur

The next step is to drag the Order Date to the Rows shelf and right click on it. Next, select the Discrete option as shown below.

Output:

Imgur

After completing the above steps, drag the newly created Date Name field next to the Order Date variable. This will generate the below output.

Output:

Imgur

Max

This is a common function used in dimensions, measures and date fields. In date calculations, it returns the maximum (or most recent) date present in the record of the connected data source, or the date expression passed in the function. The first step is to create a calculated field, Max date, and pass the variable Order Date, as an argument to the Max() function.

Output:

Imgur

Next, drag the Max date field to the Rows shelf to get the output.

Output:

Imgur

The output above returns the maximum date present in the record, which in the case of Order Date turns out to be December 30, 2019.

Conclusion

In this guide, you learned how to create a date calculation in Tableau. You also learned the common functions that can be used while dealing with date or time variables. This will help in strengthening your analytics and visualization capabilities when dealing with date variables in Tableau.

To learn more about visualization and data analysis using Tableau, please refer to the following guides:

7