DATEDIFF Tableau: How to use it for Analysis?

According to a survey, over 64,200 businesses use Tableau to analyze and visualize datasets for making Data-driven decisions. Tableau provides you with a rich set of features and templates for building interactive Dashboards, Charts, Graphs, and Visualizations. Since Tableau provides various in-built functions to implement Data Analysis, developers do not want to rely on separate platforms to analyze and process the given data. One such is the DATEDIFF Tableau function, which allows you to effectively process and analyze data-level information.

Table of Contents

In this article, you will learn about Tableau, the DATEDIFF Tableau function, and how to use the DATEDIFF Tableau function for analyzing dates level information.

Prerequisites

Fundamental knowledge of BI functions.

What is Tableau?

Developed by Pat Hanrahan, Christian Chabot, and Chris Stolte in 2003, Tableau is one of the most prominent Business Intelligence tools that allow you to implement the process of Data Visualization and Analysis. With Tableau, you can create interactive Charts, Graphs, Dashboards, and Reports to make Data-driven decisions. Since Tableau provides you with readily available Templates and Widgets, you can quickly build attractive Dashboards and Reports by just dragging and dropping necessary fields. Furthermore, Tableau has various built-in functions to implement Data Analysis for analyzing and processing data, making it easier for you to further perform Data Visualization with the clean and processed data.

For further information on Tableau, check out the official website here.

Syntax of the DATEDIFF Tableau function

The DATEDIFF function is one of the mainly used built-in functions of Tableau, which allows you to calculate the difference between the two given dates. The basic syntax of the DATEDIFF function is given below.

DATEDIFF(date_part, date1, date2)

Where,

date_part parameter is the part of the date like day, month, and year, which you want to use in your computation.
date1 and date2 are the respective starting and ending dates for which you are about to find the difference.

Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up Data Integration for 150+ Data Sources (including 40+ Free sources) and will let you directly load data from sources like Tableau to a Data Warehouse or the Destination of your choice. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.

Get Started with Hevo for Free

Let’s look at some of the salient features of Hevo:

Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
Connectors: Hevo supports 100+ Integrations to SaaS platforms FTP/SFTP, Files, Databases, BI tools, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake, Firebolt, Data Warehouses; Amazon S3 Data Lakes; Databricks; and MySQL, SQL Server, TokuDB, MongoDB, PostgreSQL Databases to name a few.
Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

How to use the DATEDIFF Tableau function for Data Analysis?

In the below steps, you will see how to use the DATEDIFF Tableau function to analyze and calculate differences between two dates of the given dataset. For this tutorial, you will use a Sales dataset that comprises Total Sales for each month based on the year 2009 to 2012, as shown below.

The above data set collectively shows the total sales for each month with the year of order from 2009 to 2012. However, you cannot flexibly analyze the sales data in terms of date. For analyzing Sales data with respect to date dimension, you have to implement basic calculations using Data Parameters in Tableau.

Initially, you have to drag fields from the Data pane drop them into the cards and shelves present in every Tableau Worksheet. While dragging and dropping fields, you get a dialogue box with aggregation options like SUM, AVG, and MEDIUM. You can use these aggregation options to customize your fields before dropping them into the Tableau Worksheet. In this case, select the MY(Order Date) option, as shown in the below image.

Then, you have to add the Sales field with its aggregate as SUM, as shown in the below image.

In the next step, you have to implement the year-to-date (YTD) calculation to get the total sales done between two given time periods using the DATEDIFF Tableau function.
In the further steps, you will see some use cases based on the DATEDIFF Tableau function. The first case is to return YTD Sales if the current year is the same as the reference date and the current month is the same or earlier than the reference date.
For implementing this use case, open a new calculated field window in Tableau and name it as “Current YTD Sales.”
Then, in the newly created calculated field, execute the code, as shown below.

After running the above code, you get the output that resembles the following image. You can change the “Reference Date” to get the sales data with respect to the given time period.

To get the previous year’s sales data, you have to create a new calculated field named “Previous YTD Sales.” Then, execute the code given below for getting the previous year’s Sales numbers.

After executing the above code, you will get the Total Sum of Sales for the year 2010 with respect to the reference date. Since our reference date is 6/1/2011, you will get Total Sales for the prior year, 2010, as shown in the image below.

To summarize the above operation, the DATEDIFF Tableau function takes specific parts (month, day, year) of the Order Date and Reference Date to return the difference between those two dates. At the back end, the DATEDIFF Tableau function will return the difference between date parts (month, day, year) based on the first date (Order Date) and the second date (Reference Date).
Then, it calculates the specific value (Total Sales) with respect to the date range determined from the difference between the Order Date and Reference Date.
You can also sum all returned values from each calculated field to get the Total Sales figure for previous YTD sales and current YTD Sales without using any additional Filters or Date Fields.
For getting the Total Sales figure for previous YTD sales and current YTD sales, you have to just remove the “MY (Order Date)” field from the “Rows” segment of the Tableau Worksheet.

After removing the Date Field, you get the Total Sum for both calculated fields, such as Previous YTD Sales and Current YTD Sales. Further, the Total Sales field is determined by summing up the Total Sales for both previous YTD Sales and current YTD Sales.
You can further disaggregate prior YTD Sales and current YTD Sales by region, such as Central, East, South, and West. For disaggregating the sales with respect to the region, you have to drag the “Region” field from the data pane and drop it into the Rows segment in the worksheet.
Now, you will acquire the relevant previous YTD Sales and current YTD for the four separate regions by disaggregating the calculated values, which will automatically configure the Sales (Total Sales) field, as shown in the image given below.

In the next step, you are about to calculate the Total Sales for the previous month according to the reference date. Then, you will calculate the Total Sales for the previous year’s previous month with respect to the Reference Date. For example, if the Reference Date is 6/1/2011, the Total Sales of the previous year’s previous month are simply the Total Sales that occurred in May 2011. This is because May is the previous month, and 2011 is the previous year as per the Reference Date.
For getting the Total Sales for the previous month according to the reference date, create a new calculated field named “Previous Month’s Sales” and execute the command as shown below.

Then, to get the Total Sales for the previous year’s previous month with respect to the Reference Date, create a new calculated field named “Previous Month’s Sales (Last Year)” and execute the command as shown below. In the below command, you included “13” since you are looking back thirteen months to get the total sales for the previous year’s previous month without including the current month. In order to include the current month, you have to replace “13” with “12.”

After running both the calculated fields, such as “Previous Month’s Sales” and “Previous Month’s Sales (Last Year),” you will get an output that resembles the following image.

In the above output, you can see that the worksheet displays the total sales value for May 2011 and May 2012. Both the Total Sales figures are based on the outputs of the calculated fields, such as “Previous Month’s Sales” and “Previous Month’s Sales (Last Year),” respectively.
In the next step, you are about to calculate the Total Sales for the previous six months of the current year (2011) and last year (2010) based on the Reference Date.
For determining the Total Sales for the previous six months of the current year, you have to create a new calculated field named “Previous 6 month Sales” and execute the command given below.

Then, to determine the Total Sales for the previous six months of the last year (2010), you have to create a new calculated field named “Last 6 months (Last Year)” and execute the command given below.

After executing both the calculated fields, such as “Previous 6 Month Sales” and “Last 6 months (Last Year),” you will get an output that resembles the following image.

In the above output, you can see that the worksheet shows the Total Sales figures for the previous six months of the current year, i.e., from March 2011 to October 2010). Similarly, you can also see the Total Sales value of the previous six months of the last year, which is from March 2010 to October 2009.
Then, you can disaggregate the Total Sales values of each calculated field by segmenting them based on the region. For disaggregating total sales with respect to the region such as Central, East, South, and West, you have to drag the “Region” field from the data pane and drop it into the Rows segment in the worksheet.
Now, you will get the detailed output containing Total Sales acquired from each calculated field based on regions, which are finally summed up to get the Overall Sales, as shown in the image below.

On following the above-mentioned steps, you used the DATEDIFF Tableau function to successfully analyze and process the information in the form of dates.

Conclusion

In this article, you learned about Tableau, the DATEDIFF Tableau function, and how to use the DATEDIFF Tableau function for analyzing dates level information. This article mainly focused on explaining and implementing data analysis using the DATEDIFF Tableau function. However, you can also explore and implement other date functions such as DATEADD, DATENAME, DATEPART, and DATETRUNC. In case you want to export data from a source of your choice such as Tableau into your desired Database/destination then Hevo Data is the right choice for you!

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources like Tableau and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial 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 learning about the DATEDIFF Tableau function! Let us know in the comments section below!

Ishwarya M Technical Content Writer, Hevo Data

Ishwarya is a skilled technical writer with over 5 years of experience. She has extensive experience working with B2B SaaS companies in the data industry, she channels her passion for data science into producing informative content that helps individuals understand the complexities of data integration and analysis.