10 Dec Tableau – Date Calculations

Date calculations in Tableau are used to manipulate and analyze date and time fields. Let us see some examples to understand the concept.

Types of Date Calculations

The following are some common types of date calculations. Consider fields such as Date Field 1, Date Field 2, etc.

Basic Date Calculations

1. Date: TODAY() (current date)
2. Date Part: YEAR([Date Field]), MONTH([Date Field]), DAY([Date Field]) (extract year, month, day)
3. Date Difference: DATEDIFF(‘day’, [Date Field1], [Date Field2]) (calculate difference)
4. Date Add: DATEADD(‘day’, 30, [Date Field]) (add days, months, years)

Date Manipulation Calculations

1. Date Trunc: TRUNC([Date Field], ‘month’) (truncate to month, quarter, year)
2. Date Floor: FLOOR([Date Field], ‘quarter’) (round down to quarter, year)
3. Date Ceiling: CEILING([Date Field], ‘year’) (round up to year)
4. Date Format: DATETOSTRING([Date Field], ‘YYYY-MM-DD’) (format date)

Relative Date Calculations

1. Yesterday: TODAY() – 1
2. Last Week: TODAY() – 7
3. Last Month: TODAY() – 30
4. Last Quarter: TODAY() – 90
5. Year-to-Date (YTD): TODAY() – (TODAY() – 1) % 365

Period-over-period (PoP) Calculations

1. Year-over-Year (YoY): DATEADD(‘year’, -1, [Date Field])
2. Quarter-over-Quarter (QoQ): DATEADD(‘quarter’, -1, [Date Field])
3. Month-over-Month (MoM): DATEADD(‘month’, -1, [Date Field])

Advanced Date Calculations

1. Age: DATEDIFF(‘day’, [Birthdate Field], TODAY())
2. Next/Previous Occurrence: DATEADD(‘day’, 1, [Date Field]) or DATEADD(‘day’, -1, [Date Field])
3. Date Range: DATEDIFF(‘day’, [Start Date Field], [End Date Field])

Example

We will use the data calculation to find the average days it takes to ship products in all the regions.

Let us create a new sheet and name it Date Calculations in the same Amit_Tableau_Calculations.twb Tableau Workbook. We created the Amit_Tableau_Calculations.twb Tableau workbook in the Numeric Calculations lesson.

Go to the Analysis menu and click on it. After that, click Create Calculated Field:

Click on the arrow to display all the built-in functions. From the dropdown, click Date to display only the date functions:

Set the name of the calculation. We will set it to Date_Difference. From the right, select the DATEDIFF() function. After selecting, on the left, set the formulae i.e., DATEDIFF(‘day’, [Order Date], [Ship Date], ‘Monday’ ). Here:

The DATEDIFF displays the difference between two dates (Order and Ship date here) and the result in the day units. The week of the day is set to Monday. We will get the days it takes to ship products.

The formulae can be seen and there are no error messages. Click OK:

The new field Date_Difference is visible:

Drag the Region to the Rows shelf and the Date_Difference to the Text in the Marks shelf. Through this, we will get the days it takes to ship products in all the regions:

Right-click on the SUM(Date_Difference) in the Marks pane and select AVERAGE to get the average of the date difference (Order and Ship) region-wise. Here, we are changing the aggregation level to average:

Now, the average date difference can be seen. This shows the average days it takes to ship products in all the regions.

If you liked the tutorial, spread the word and share the link and our website Studyopedia with others.

For Videos, Join Our YouTube Channel: Join Now

Read More:

• Get Data from Excel in Power BI
• Create a Line Chart in Tableau
• Create a Bar/ Column Chart in Tableau
• Create a Pie Chart in Tableau
• Create a Map in Tableau
• Create a Tree Map in Tableau
• Create a Scatter Plot in Tableau
• Create a Bubble Chart in Tableau
• Tableau – Filters and its types
• Tableau – Filter Dimensions
• Tableau – Filter Measures
• Tableau – Filter Dates
• Create an Area Chart in Tableau
• Create a Heat Map in Tableau
• Create a Histogram in Tableau