DATEDIFF Tableau: How to use it for Analysis? | A Comprehensive Guide 101

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.

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.

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. 

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.

• 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. 

• 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.

• 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.

• 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).”

• 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”.
• 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.

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! 

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 150+ sources (including 60+ 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. Sign up for 14-day free trial today.

FAQ

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!