Tableau Essentials: Calculated Fields – Date Functions

Data

Tableau Essentials: Calculated Fields – Date Functions

Not everyone is a Tableau guru, at least not yet. To help Tableau rookies, we’re starting from square one with the Tableau Essentials blog series. The series is intended to be an easy-to-read reference on the basics of using Tableau Software, particularly Tableau Desktop. Since there are so many cool features to cover in Tableau, the series will include several different posts.

Today, we’ll continue our exploration of the functions you can use when you create a calculated field. We covered Logical Functions and Number Functions in the two previous articles on this topic.

Now, we’ll cover Date Functions. To bring up the Calculated Field window, right-click anywhere in the Data window (i.e. sidebar) to bring up that menu. Then, select Create Calculated Field …

Create Calculated Field ...

The Calculated Field window will appear like so:

Calculated Field Window

If you right-clicked on a particular dimension or measure to create your calculation, then it will appear in the Calculated Field window in the formula text area. To filter the functions to show only the Date Functions, use the drop-down box and select Date.

Date

Now that we’ve got our functions selected, let’s first define the potential values for date_part, which is used in many of the Date Functions. Date_part can have the following values:

  • Second (0-60)
  • Minute (0-59)
  • Hour (0-23)
  • Day (1-31)
  • Weekday (1-7 or by name, i.e. “Sunday,” etc.)
  • Week (1-52)
  • DayofYear (1-365)
  • Month (1-12 or by name, i.e. “January,” etc.)
  • Quarter (1-4)
  • Year (four-digit representation)

Now that you know the different values, let’s examine the Date Functions:

DATEADD Function

DATEADD(date_part, interval, date)

The DATEADD function allows you to specify a portion of a date and increase it. The number that you use for the interval will change the date by increasing the date_part. Example:

DATEADD(‘day’, 15, #2014-09-15#) = 2014-09-30 12:00:00 AM

DATEDIFF Function

DATEDIFF (date_part, date1, date2, start_of_week)

This function allows you to return the difference between date1 and date2 expressed in units determined by date_part. The start_of_week parameter is optional, and if it is not defined, then the start of the week is determined by the associated data source. Example:

DATEDIFF(‘day’, #2014-09-15#, #2014-09-20#) = 5

DATENAME Function

DATENAME(date_part, date, [start_of_week])

You can use this function to return the date_part parameter of the date as a string. Again, the start_of_week parameter is optional. Example:

DATENAME(‘month’, #2014-09-01#) = “September”

DATEPARSE Function

DATEPARSE(format, string)

This function essentially works in the reverse of DATENAME by converting a string into a date/time with your specified format. If the string does not match the date/time format, then it will return a value of Null. Example:

DATEPARSE(“dd.MMMM.yyyy”, “29.September.2014”) = #September 29, 2014#

This function is only available for Microsoft Excel, MySQL, Oracle, PostgreSQL, Tableau data extract and text file data sources. More on that here.

DATEPART

DATEPART(date_part, date, start_of_week)

The DATEPART function allows you to return a specified date_part as an integer. Again, the start_of_week parameter is optional. Example:

DATEPART(‘year’, #2014-09-30#) = 2004

When the date_part is set to weekday, the start_of_date parameter is not used because Tableau uses a fixed order to apply offsets.

DATETRUNC

DATETRUNC(date_part, date, start_of_week)

This function truncates the date to the accuracy of the date_part that you specify in the function. In other words, it rounds towards that date_part. Example:

DATETRUNC(‘quarter’, #2014-09-29#) = 2014-07-01 12:00:00 AM

If the start_of_week is omitted, then it is determined by the data source.

DAY

DAY(date)

This function returns the day of the specified date as an integer. Example:

DAY(#09-29-2014#) = 29

ISDATE

ISDATE(string)

This is a logical test that is also included in the list of Logical Functions. It tests a string to determine if it is a valid date (true/false). Example:

ISDATE(“September 29, 2014”) = true

MAX Function

MAX(expression) or MAX(expr1,expr2)

The MAX function exists in several categories of functions, including the Date Functions. The MAX function returns the maximum of a single expression across all records or the maximum of two expressions for each record. The two arguments must be the same type. This function will return a value of NULL if either argument is NULL. Example:

MAX(#2014-09-15#,#2014-10-15#) = 2014-10-15 12:00:00 AM

MIN Function

MIN(expression) or MIN(expr1,expr2)

Like the MAX function above, the MIN function is commonly used as a Number Function but can also be used with dates. The MIN function returns the minimum of a single expression across all records or the minimum of two expressions for each record. MIN returns a value of NULL if either of the two arguments is NULL. The two arguments must be of the same type. Example:

MIN(#2014-09-15#,#2014-10-15#) = 201-09-15 12:00:00 AM

MONTH

MONTH(date)

This function returns the month of the specified date as an integer, just like the DAY function does for day. Example:

MONTH(#09-29-2014#) = 9

NOW

NOW()

Returns the current date and time. Example:

NOW() = 2014-11-07 12:00:00 AM

TODAY

TODAY()

Returns the current date. Example:

TODAY() = 2014-11-07

YEAR

YEAR(date)

The YEAR function returns the year of the specified date as an integer. Example:

YEAR(#09-29-2014#) = 2014

Calculated Fields

Calculated fields can add a whole new layer of insight to your Tableau dashboards. The possibilities are practically endless, but we’ll be covering the fundamentals, especially functions, to help you build a foundational understanding of how and when to use them. Check back for more posts covering:

Introduction

  1. Logical Functions
  2. Number Functions
  3. Date Functions
  4. String Functions
  5. Type Conversion
  6. Aggregate Functions
  7. User Functions

Another great resource for functions is Dan Murray’s best-selling guidebook, “Tableau Your Data!” It features a whole section devoted the functions we’ll be covering in this series and much, much more.

More Tableau Essentials

Want to learn more about Tableau? We have several posts outlining all of Tableau’s fantastic features. Check out the full list on our Tableau Essentials blog channel.

As always, let us know if you have any questions or comments about this post or Tableau in general. If you’re looking for personalized training or help with something bigger, contact us directly!

Want More Tableau Essentials

  1. Tableau Essentials: Chart Types – Introduction
  2. Tableau Essentials: Chart Types – The Text Table
  3. Tableau Essentials: Chart Types – Heat Map
  4. Tableau Essentials: Chart Types – Highlight Table
  5. Tableau Essentials: Chart Types – Symbol Map
  6. Tableau Essentials: Chart Types – Filled Map
  7. Tableau Essentials: Chart Types – Pie Chart
  8. Tableau Essentials: Chart Types – Horizontal Bar Chart
  9. Tableau Essentials: Chart Types – Stacked Bar Chart
  10. Tableau Essentials: Chart Types – Side-by-Side Bar Chart
  11. Tableau Essentials: Chart Types – Treemap
  12. Tableau Essentials: Chart Types – Circle View
  13. Tableau Essentials: Chart Types – Side-by-Side Circle View
  14. Tableau Essentials: Chart Types – Line Charts (Continuous & Discrete)
  15. Tableau Essentials: Chart Types – Dual-Line Chart (Non-Synchronized)
  16. Tableau Essentials: Chart Types – Area Charts (Continuous & Discrete)
  17. Tableau Essentials: Chart Types – Scatter Plot
  18. Tableau Essentials: Chart Types – Histogram
  19. Tableau Essentials: Chart Types – Box-and-Whisker Plot
  20. Tableau Essentials: Chart Types – Gantt Chart
  21. Tableau Essentials: Chart Types – Bullet Graph
  22. Tableau Essentials: Chart Types – Packed Bubbles
  23. Tableau Essentials: Formatting Tips – Introduction
  24. Tableau Essentials: Formatting Tips – Custom Shapes
  25. Tableau Essentials: Formatting Tips – Labels
  26. Tableau Essentials: Formatting Tips – Color
  27. Tableau Essentials: Formatting Tips – Tooltips
  28. Tableau Essentials: Formatting Tips – Maps
  29. Tableau Essentials: Calculated Fields – Introduction
  30. Tableau Essentials: Calculated Fields – Logical Functions
  31. Tableau Essentials: Calculated Fields – Number Functions
  32. Tableau Essentials: Calculated Fields – Date Functions
  33. Tableau Essentials: Calculated Fields – String Functions
  34. Tableau Essentials: Calculated Fields – Type Conversion
  35. Tableau Essentials: Calculated Fields – Aggregate Functions
  36. Tableau Essentials: Calculated Fields – User Functions

More About the Author

Robert Curtis

Managing Director, APAC
Building Solutions with InterWorks at Corinium’s Data Architecture Conference in Melbourne InterWorks was a proud sponsor of the Data Architecture Conference hosted by Corinium in Melbourne on 21 and 22 June 2023. Hundreds of ...
Virtual Event Recap: Deep Dive of Tableau Parameters For our June Lunch and Learn, we did a deep dive on parameters for our friends across Australia and Singapore. Tableau is the premier ...

See more from this author →

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

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!