_edited_edited.png)
Tableau has tons of different date and time-related functions for you to use. we’ll continue our exploration of the functions you can use when you create a calculated date fields
Date calculations:
Dates are a common element in many data sources. If a field contains recognizable dates, it will have a date or date time data type. When date fields are used in the viz they get a special set of functionality, including an automatic date hierarchy drill down, date-specific filter options, and specialized date formatting options.
Date functions allow you to manipulate dates in your data source.Date functions sometimes reference date-specific elements, including the date_part argument, the optional [start_of_week] parameter, and date literals (#)
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 …
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.
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:
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:
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:
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:
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:
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.
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)
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:
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:
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:
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:
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:
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(#03/05/2018#)=3
NOW
NOW()
Returns the current date and time. Example:
now()=2018-03-05 3:29:00 PM
TODAY
TODAY()
Returns the current date. Example:
today()=2023-03-05
YEAR
YEAR(date)
The YEAR function returns the year of the specified date as an integer. Example:
year(#03-05-2018#)=2018
Example of Date Calculations:
In Tableau Desktop, connect to the Sample-Superstore saved data source, which comes with Tableau.
Open a worksheet.
From the Data pane, under Dimensions, drag Order Date to the Rows shelf.
On the Rows shelf, click the plus icon (+) on the YEAR(Order Date) field.QUARTER(Order Date) is added to the Rows shelf and the view updates.
On the Rows shelf, click the plus icon (+) on the QUARTER(Order Date) field to drill down to MONTH(Order Date).
Select Analysis > Create Calculated Field.
In the calculation editor that opens, do the following:
Name the calculated field, Quarter Date.
Enter the following formula: DATETRUNC('quarter', [Order Date])
When finished, click OK.The new date calculated field appears under Dimensions in the Data pane. Just like your other fields, you can use it in one or more visualizations.
From the Data pane, under Dimensions, drag Quarter Date to the Rows shelf and place it to the right of MONTH(Order Date). The visualization updates with year values. This is because Tableau rolls date data up to the highest level of detail.
On the Rows shelf, right-click YEAR(Quarter Date) and select Exact Date.
On the Rows shelf, right-click YEAR(Quarter Date) again and select Discrete
You can find tableau date related cheat sheets in this link. The workbook has a table listing all of the calculated fields available. You can use the filters to choose specifically what you’re looking for, then the table will show you the name of the calculated field as well as the folder they are in. You can then simply download the workbook, copy the calculated field (and any dependencies), then paste them into your workbook.
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.
Logical Functions
These are the logical functions in tableau
Number Functions:
These are the number functions in Tableau:
String Functions:
These are sting functions in Tableau:
Type Conversion:
These are some type conversions in Tableau:
Aggregate Functions
These are Aggregate functions in Tableau:
User Functions
These are some user functions in Tableau:
Finally the cheat mentioned in this blog for date calculations becomes very handy for you.
Will try to Explain more about the calculated fields in my next blog.
Thank you for reading!
Comments