Tableau Deep Dive: Dates – More Date Functions

Data

Tableau Deep Dive: Dates – More Date Functions

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

We’re continuing our Deep Dive into Dates with further exploration into date specific functions. In this article, we’ll cover DATEADD, DATEDIFF and DATETRUNC. My goal is not only to show you how to use them but to give you some real world examples of when they’d be particularly useful.

We’re going to start with our hypothetical Superstore data source and build a use case that will become more and more complex as we add new functions to our tool belt. Let’s start by adding to a date field.

DATEADD()

DATEADD allows you to add to a date field. If I tell you that I’m going to add +2 to March 2, 2016, your first question should be two of what? Two days? Two months? Two quarters? Tableau allows you specific the unit of measure that you want to add in the function by specifying the date part. Using a negative value allows you to subtract from a date.

Let’s take our Order Date field from the Superstore.xlsx data source and assume that our business has set a goal to ship all products within three days of the order being placed. We’ll use DATEADD to create the new field:

Shipping Goal Date calc

With this new field, we can compare the goal shipping date to the actual shipping date:

Shipping Goal Achieved? calc

Our visualization starts with a distinct count of Order ID on Columns with Sub-Category on Rows. Finally, let’s drag our Boolean field onto Color to divide each bar into shipping on time vs. shipped late:

Our Tableau view w/ Boolean field

We can see that across all of our products, we have some significant challenges as a business to ship our products more quickly.

DATEDIFF()

Let’s extend our example above by going deeper into our analysis. In the bar chart above, we simply see whether a product was shipped on time or not. It would be interesting to see the variance of how fast or how slow the product is shipped compared to the goal. We’ll use DATEDIFF to make this analysis.

We’re going to find the average difference between the goal shipping date and the actual shipping date:

DATEDIFF() calc

If I simply had Ship Date – Shipping Goal Date, Tableau will produce a result back to me in days by default. For this example, that works fine. The big advantage of DATEDIFF is that you can specify what unit you want to return back (weeks, months, etc). I put ship date in the start date and the goal shipping date in the end date, so any products that were shipped late will show up as a negative value. Let’s clean this up so it’s a bit easier for our reader to understand with another calculation:

Shipping on Time Result calc

I’ve put the calculation into a text table:

Calc in text table

While most of our products are shipping later than we’d like according to our initial bar chart, DATEDIFF reveals that if we can expedite our processes by a single day nearly all of our product sub-categories will meet our shipping goal.

DATETRUNC()

Let’s add another wrinkle to the above scenario. Instead of having a goal shipping date for all of our products, let’s say that our products ship on the first of each month. We receive orders throughout the entire month, but the ship date will always be the first day of the following month.

DATETRUNC is an easy way to solve this problem. This function truncates a date to the specified date part. It removes all of the date parts that are lower in the date hierarchy. For instance, if we put March 6, 2016 into the DATETRUNC() function with the date part set to month, the date will truncate to March 2016 (or March 1, 2016). If we set the date part to quarter, then that same date would truncate to Q1 2016 (or January 1, 2016).

Let’s solve our use case. If we just use DATETRUNC, we’ll get the first day of the month. That won’t work as it’s impossible to ship a product before its ordered … unless Professor X manages our inventory. So, we’ll have to add a month to our date field first and then truncate back down to the month date part.

DATETRUNC () calc

We can use this function on our visualization in combination with our DATEDIFF to see what the ship date will be and the days it takes to ship from when the product was ordered. Here’s the DATEDIFF calculation for this view:

Plus DATEDIFF ()

And here’s our final view:

Add month to date field

Hopefully, these examples starts to spark your imagination on how you can use these date functions to apply to your data and your requirements.

What’s Next?

In the fourth article, we’ll explore one of the techniques that I used in this article with exact dates and making them discrete. I’ll use the ATTR function to illustrate another use case that might be helpful for you. Until then, any questions or thoughts are appreciated in the comments section below.

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Analytics Consultant / ANZ Practice Lead
An Interactive Guide to Public Toilets in Australia Traveling requires planning. Where do you want to go? How will you get there? Where will you stay? What will you eat? These are the ...
Tableau Desktop Now Available in Dothraki With aims to broaden their global customer base, Tableau Software (NYSE: DATA) announced today a Dothraki language version. The ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK