Tableau Functions

What are the functions in Tableau?

Functions in Tableau refer to the built-in functions that it provides to allow you to perform data manipulation and calculation and create complex visualizations. Tableau supports a vast category of functions such as number functions, date functions, string functions, logical functions, etc. A sample Tableau function is provided below.

SUM(Revenue) returns the total revenue value in your dataset.

Types of Tableau Functions

Tableau supports several functions to support calculation, data visualization, and analysis. This section focuses on providing an overview of functions in Tableau, with examples in each category and a quick description and syntax for your reference. The list of Tableau functions includes:

#1 – Number Functions

Tableau provides many built-in number functions to enable you to perform calculations on fields containing numerical values. 

Some of the notable number functions include:

1. ABS – Returns the absolute value of a number, i.e., positive, regardless of whether the number is positive or negative.

For example, ABS(-11) returns 11.

2. CEILING – This function rounds up a number with decimal values to the nearest integer of the same or higher value. 

For example, CEILING(3.9) returns 4.

3. DIV – Division function returns the integer value of a division of two numbers. 

For example, DIV(5,3) returns 1.

4. EXP – Exponential function returns e raised to the power of a number. 

For example, EXP(1) returns 2.71.

5. FLOOR – It’s similar to the CEILING function; however, it rounds down a number with decimal values to the nearest integer of the same or lower value. 

For example, FLOOR(3.4) returns 3.

6. LOG – The logarithm function returns a log value for a number. 

For example, LOG(9,3) returns 2.

7. POWER – The power function raises the number to the input power value. 

For example, POWER(3,2) returns 9.

8. ROUND – This function rounds a specified number to an input number of digits. 

For example, ROUND(4.6,2) returns 4.60.

9. SIGN – It returns one of the values from -1, 0, or 1 depending on the signage of a number. 

For example, SIGN(-9) returns -1.

10. SQRT – It returns the square root of a number. 

For example, SQRT(16) returns 4.

#2 – String Functions

These functions help you manipulate string data, such as text, using various built-in string functions. Tableau stores the strings based on locale and language.

Some of the important string functions that you can use in Tableau include:

1. CONTAINS – Use this function to identify whether a string contains a specific substring. 

For example, CONTAINS(“Tableau Function”, “Function”) returns true.

2. LEFT – It returns the left-most number of characters in a specified string. 

For example, LEFT(“Tableau”,3) returns Tab.

3. LEN – Length function returns the length of a specified string. 

For example, LEN(“Tableau”) returns 7.

4. LOWER – It returns all the input strings in a lowercase format. 

For example, LOWER(“Tableau Functions”) returns tableau functions.

5. REPLACE – This function searches a substring in a specified string and then replaces it with the replacement string. 

For example, REPLACE(“Tableau Function”, “Function”, “Desktop”) returns Tableau Desktop.

6. SPLIT – Split function returns a substring in a specified string based on the input delimiter value. It divides the input string into multiple tokens using the delimiter and then returns the substring. 

For example, SPLIT(“T|a|B”, “|”, 3) returns B.

7. TRIM – use this function to trim or remove the leading and trailing spaces in a string. For example, TRIM(“ Tableau Functions“) returns Tableau Functions.
8. UPPER – It returns all the input strings in an uppercase format. 

For example, UPPER(“Tableau Functions”) returns TABLEAU FUNCTIONS.

#3 – Date Functions

These functions allow you to manipulate date values, apply date filters, format date fields, etc. Some of the vital date functions include:

1. DATE – It returns the date value for a given string, number, or date expression. 

For example, DATE(“June 22, 2024”) returns 22-06-2024.

2. DATEDIFF – It returns the date difference between two dates expressed in the units of date_part, i.e., weeks, days, years, etc.

For example, DATEDIFF(‘week’,#2024-06-23#, #2024-06-24#, ‘monday’) returns 1 as here we have specified the start_of_week as Monday. Date_part is week whereas the specified dates include the start and end dates.

3. DATEPART – It extracts the specified date part from the specified date. 

For example, DATEPART(‘year’, #2024-06-22#) returns 2024.

4. DAY/MONTH/QUARTER/YEAR – This function extracts the day of the month/month/quarter/year as an integer from an input date, depending on the function you use. 

For example, DAY(#June 22, 2024#) returns 22. Similarly, MONTH(#June 22, 2024#) returns six and so on.

5. ISDATE – It validates whether the input string follows a valid date structure and returns a Boolean value, i.e., True/False. 

For example, ISDATE(06/22/2024) returns True.

6. ISOQUARTER/ISOYEAR/ISOWEEKDAY/ISOWEEK – Depending on the function you use, they return ISO8601 week-based quarter/year/weekday/week for the input date. 

For example, ISOQUARTER(DATE(“05/27/2024”)) returns 2.

7. NOW – It returns the current local system date and time value. There are no input parameters applicable to this function. 

For example, NOW() returns 22-06-2024 22:53:22.

8. TODAY – Similar to NOW, this function returns the current local system date and doesn’t take any inputs. 

For example, TODAY() returns 22-06-2024.

#4 – Functions supporting Type Conversion

These functions enable you to convert a data field from one data type to another. For example, converting a date field to a string and vice versa. The key functions in this category include:

1. DATE/DATETIME – These functions allow you to convert a number, string, or date into DATE or DATETIME, depending on the function you use. 

For example, DATE(employee_joining_date) or DATETIME(“June 22, 2024 07:59:00”) returns the date and date time format for your input data.

2. FLOAT/INT – The FLOAT function converts a decimal number to a floating point number. Similarly, INT converts a decimal number to an integer value. 

For example, FLOAT(4.32) returns 4.32 whereas INT(10/3) returns 3.

3. MAKEDATE/MAKEDATETIME – These functions return the date value or datetime value from the input date, month, and year. 

For example, MAKEDATE(2024,06,22) returns 22-06-2024.

4. STR – This function casts an input value into a string. 

For example, STR(“123ABC”) returns a string 123ABC.

5. Boolean expressions – You can map a Boolean value to an integer, float, or string value. However, you can’t map it to a date.

#5 – Logical Functions

These functions enable you to evaluate whether an expression is true or false. Some of the key logical functions include:

1. IF/ELSEIF/END/AND/OR – This logical operator performs a logical concurrence on the expression and returns a Boolean value. 

For example, IF name = ‘Tableau’ AND value = ‘Function’ THEN “Tableau Function” END. The same applies to OR as well.

2. CASE – This function allows you to evaluate an expression and compare it against the list of values. Once a <value> is matched against the expression, corresponding <return> values are returned. 

For example, 

CASE [Grade]

WHEN ‘A’ THEN ‘First Class’

WHEN ‘B’ THEN ‘Second Class’

ELSE ‘Third Class’

END

3. IIF – This function validates if a condition is met and then returns the <then> value if True. Otherwise, the optional value is returned. 

For example, IIF([Profit] > 0,’Profit’, ‘Loss’). If no optional values are defined then null is returned.

#6 – Aggregate Functions

These functions allow you to perform aggregation or change the data granularity in your input data. Some of the essential functions include:

1. AVG – AVG returns the average of all the values in the input data. 

For example, AVG(Sales) returns average sales value across all the years.

2. COUNTD – It returns the distinct number of items in a group. 

For example, COUNTD(Customer ID) returns the total number of distinct customers in the dataset.

#7 – Pass-through Functions

These functions, also known as RAWSQL, enable you to send SQL expressions directly to the database and avoid any interpretation by Tableau. Some of these key functions include:

1. RAWSQL_BOOL – It returns a Boolean value from an input SQL statement. 

For example, RAWSQL_BOOL(“%1 > %2”, [WPI],[CPI])

2. RAWSQL_DATE – It returns a date value from an input SQL expression. 

For example, RAWSQL_DATE(‘%1’, [Ship Date])

#8 – Table Calculation Functions

These functions help you to perform calculations on the values in a Tableau table. Some of the key functions include:

1. RANK – It evaluates an expression and then sorts the data in either ascending or descending order per the input choice. 

For example, RANK(Order Id,’asc’)

2. RUNNING_SUM – It returns a running sum for a given expression. For example, the running sum for the values listed below is as follows:

#9 – Spatial Functions

These functions help you to perform any advanced spatial analysis and combine spatial files with data in text files or spreadsheets. Key spatial functions include:

1. AREA – It returns the total surface area for an input polygon. 

For example, AREA([Building], ‘miles’) will return the area value in miles.

2. INTERSECTS – It returns a Boolean value highlighting if the geometric figures overlap. 

For example, INTERSECT(geometry1, geometry2) returns True/False.

#10 – Predictive Modeling Functions

Using these functions, you can quickly generate predictions that you can visualize, perform data manipulation, and extract for your requirements. Some of the critical predictive modeling functions include:

1. MODEL_PERCENTILE – It returns a probability value between 0 and 1. 

For example, MODEL_PERCENTILE( SUM([Revenue]),COUNT([Order Id]))

#11 – Additional Functions

These are additional functions that include:

1. REGEXP_REPLACE – It returns a copy of an input string where the regular expression pattern is replaced with the input replacement string. It is supported by multiple data sources such as Hadoop, Google BigQuery, Excel, etc. 

For example, REGEXP_REPLACE(‘Tableau Function’, ‘\s’, ‘-‘) = ‘Tableau-Function123’

#12 – User Functions

These functions can help you create user filters or row-level security filters for your Tableau visualization. Some of the key user functions include

1. USERDOMAIN() – This function returns the current user domain value.

Note: Ensure that you always look for Tableau release notes and an official guide for more details. You may also follow a Tableau functions cheat sheet for quick function reference.

Importance Things to Note

• Depending on your requirements, you may consider using the appropriate Tableau functions in your calculations.
• It is recommended that you refer to official Tableau documentation along with the Tableau functions tutorial for better usage clarity.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Tableau Functions. Here we learn different types of functions with examples, which includes average, number, string, table, etc. and points to note. You can learn more from the following articles –

Context Filter in Tableau

Tableau Joins

Tableau Order of Operations