Tableau Functions make Tableau stand out in business Analytics proves that Tableau is not just another tool to represent data using pretty looking graphs. Using Tableau Functions, one can understand, analyse, organise, and report data like never before.
This article on "Tableau Functions" is dedicated to walking us through various functions available and used to manipulate data.
Number Functions
In Tableau fields, when the user might need to apply some mathematical operations. In such scenarios, the number functions (one of the Tableau Functions) come handy. The number functions available in Tableau are as follows:
ABS
The ABS Tableau Functions are used to Return the absolute value of the given value. The Syntax of the ABS function is as shown below.
Syntax:
ABS(Numerical Value)
Example:
ABS(-15) = 15
ACOS
The ACOS tableau functions are used to return the arc cosine value of the given number. The results are often returned in the measures of Radian. The Syntax of ACOS Function is as follows.
Syntax:
ACOS(Numerical Value)
Example:
ACOS(0.5) = 1.04719
ASIN
The ASIN tableau functions are used to return the arc sine of a given number. The results are often returned in the measures of Radian. The Syntax of the ASIN Function is as follows.
Syntax:
ASIN(Numerical Value)
Example:
ASIN(0.5) = 0.5235
ATAN
The ATAN function in the Tableau is used to return the arc tangent value of a given number. The results are often returned in the measures of Radian. The Syntax of the ATAN Function is as follows.
Syntax:
ATAN(number)
Example:
ATAN(180) = 1.565
ATAN2
The ATAN2 Tableau Functions are used to return the arc tangent of two given numbers (x and y). The results are often returned in the measure of radians. The Syntax of the ATAN2 Function is as follows.
Syntax:
ATAN2(x-coordinate, y-coordinate)
Example:
ATAN2 (5,3) = 0.4
CEILING
The CEILING Tableau Functions are used to round up float or double values. The Syntax of the CEILING function is shown below.
Syntax:
CEILING(Numerical Value)
Example:
CEILING(2.24) = 3
COS
The COS Tableau Functions are used to find the COSINE value of a given angle. The result is given in the form of Radians. The Syntax of the COS Function is shown below.
Syntax:
COS(Angle Value)
Example:
COS(PI()/4 = 0.707)
COT
The COT Tableau Functions are used to return the COTANGENT Value of the given angle. The Syntax of the COT Function is given below
Syntax:
COT(Angle Value)
Example:
COT(PI( ) /4) = 1
DEGREES
The DEGREES Function in Tableau is used to find out the value of Radians in its Degrees. The Syntax of the DEGREES Function is shown below.
Syntax:
DEGREES (Radian Value)
Example:
DEGREES(PI( )/4) = 45.0
DIV
The DIV Function in the Tableau is used to return the integer part of a division operation, in which the first integer is divided by the second integer. The Syntax for the DIV Function is as follows.
Syntax:
DIV (First Integer, Second Integer)
Example:
DIV(20,2) = 10
EXP
The EXP function is used to find the exponential value of the given number. The Syntax of the EXP function is shown below.
Syntax:
EXP(Numerical Value)
Example:
EXP(2) = 7.389
FLOOR
The FLOOR tableau functions are used to round a number to the nearest integer of equal or lesser value. The Syntax of the FLOOR function is shown below.
Syntax:
FLOOR(Numerical Value)
Example:
FLOOR(2.234) = 2
SQUARE
The SQUARE function in Tableau is used to find out the SQUARE of the given numerical value. The Syntax of the SQUARE function is shown as follows.
Syntax:
SQUARE(Numerical Value)
Example:
SQUARE(6) = 36
ZN
The ZN function in Tableau is used for returning the expression if it is not null. Otherwise, it returns zero. Use this function to use zero values in place of null. The Syntax for the ZN function is as follows.
Syntax:
ZN(expression)
Example:
ZN(SUM(Sales)) = 23,95,894
Following the numerical functions, we will now move into the next segment to learn the STRING Functions in Tableau.
String Functions
String Functions (One of the Tableau Functions) are used to manipulate the character type data in Tableau. Following are the String Functions in Tableau.
LOWER
The LOWER Tableau functions are used to return the given upper case string in the lower case. The Syntax for the LOWER function is shown below.
Syntax:
LOWER(string)
Example:
LOWER(TABLEAU) = tableau
UPPER
The Upper Function is used to return the given lower case string in the upper case. The Syntax for the UPPER function is as follows.
Syntax:
UPPER(string)
Example:
UPPER(tableau) = TABLEAU
ASCII
The ASCII function is used to return the ASCII code for the first character in the String. The Syntax for the ASCII function is shown as follows.
Syntax:
ASCII(String Value)
Example:
ASCII(‘A’) = 65
CHAR
The CHAR function is used to return the character when an ASCII value is passed. The Syntax of the CHAR function is shown below.
Syntax:
CHAR(ASCII Value)
Example:
CHAR(65) = ‘A’
CONTAINS
The CONTAINS tableau functions are used to find if a certain substring is present in the String. The Syntax of the CONTAINS function is shown below.
Syntax:
CONTAINS(String Value)
Example:
CONTAINS(SIMPLILEARN, LEARN)
ENDSWITH
The ENDSWITH function is used to return a true if the given String ends with the specified substring. ENDSWITH ignores trailing white spaces. The Syntax is shown as follows:
Syntax:
ENDSWITH(string, substring)
Example:
ENDSWITH(“Simplilearn”, “earn”) = true
FIND
The FIND function is used to return an index position of the substring in a string or null if it is not found. If another argument start is added, the function ignores any substring instances before the index position starts. The beginning character in the string is at index 1. The Syntax for FIND is shown as follows:
Syntax:
FIND(string, substring, [start])
Example:
FIND("ABCD", "CD") = 2
LEFT
The LEFT function is used to return the left-most number of characters in the String. The Syntax of the LEFT function is shown below.
Syntax:
LEFT(String, number)
Example:
LEFT("Simplilearn", 5) = "Simpli"
RIGHT
The RIGHT function is used to return the right-most number of characters in the String. The Syntax of the RIGHT function is shown below.
Syntax:
RIGHT(String, number)
Example:
RIGHT("Simplilearn", ) = "learn"
LEN
The LEN function is implemented to return the length of the given String. The Syntax of the LEN function is given below.
Syntax:
LEN(String Value)
Example:
LEN(“Simplilearn”) = 11
TRIM
The TRIM function is used to return the string with leading and trailing spaces removed. The Syntax of TRIM is shown below.
Syntax:
TRIM(String)
Example:
TRIM(" Simplilearn ") = "Simplilearn"
LTRIM
The LTRIM function is used to return the String after removing any leading spaces. The Syntax for the LTRIM function is shown below.
Syntax:
LTRIM(String)
Example:
LTRIM(“ Simplilearn ”) = “Simplilearn”
MAX
The MAX function is implemented to return the maximum of a and b. The Syntax for the MAX function is as follows.
Syntax:
MAX(a,b)
Example:
MAX ("abc","pqrs") = "pqrs"
MID
The MID function is used to return the String starting at the starting position. The initial character in the string is position 1. If another argument length is included, the returned String includes only that number of characters. The Syntax for the MID function is shown below.
Syntax:
(MID(string, start, [length])
Example:
MID("Simplilearn", 6) = "learn"
REPLACE
The REPLACE function is used to search the String for the substring and replaces it with the replacement string. The Syntax is shown below.
Syntax:
REPLACE(string, substring, replacement)
Example:
REPLACE("Java 8", "8", "13") = "Java 13"
RTRIM
The RTRIM function is used to return a string with any trailing spaces removed. The Syntax of RTRIM is shown below.
Syntax:
RTRIM(string)
Example:
RTRIM(" Tableau ") = " Tableau"
SPACE
The SPACE function is used to return a string composed of the specified number of repeated spaces. The Syntax of the SPACE function is shown below.
Syntax:
SPACE(Number)
Example:
SPACE() = " "
SPLIT
The SPLIT function is implemented to return a substring from a string, using a delimiter character to break the string into a tokens sequence. The Syntax of the SPLIT function is shown below.
Syntax:
SPLIT(String, delimiter, token number)
Example:
SPLIT (‘a-b-c-d’, ‘-‘, 4) = ‘c
RETURNSWITH
The RETURNSWITH function is used to return true if the String starts with a substring. Leading white spaces are ignored. The Syntax for RETURNSWITH is shown below.
Syntax:
STARTSWITH(string, substring)
Example:
STARTSWITH(“Simplilearn”, “Si”) = true
With that, we have finished the String Functions in Tableau. Now, we will continue with USER Functions in Tableau.
User Functions
The USER-related details and methods are handled by the USER function (One of the Tableau Functions) in real-time. Following is the list of USER functions in Tableau:
USERNAME
The USERNAME function is used to return the name of the current user. The Syntax for USERNAME is shown below.
Syntax:
USERNAME( )
Example:
[Manager]=USERNAME( )
ISUSERNAME
The ISUSERNAME is used to return true if the current user's username matches the specified username or false if it does not match. The Syntax for the ISUSERNAME is shown below.
Syntax:
ISUSERNAME(String)
Example:
ISUSERNAME("Harry")
ISMEMBEROF
The ISMEMEROF function is used to return true if the person currently using Tableau is a group member that matches the given String. The Syntax of the ISMEMBEROF function is mentioned below.
Syntax:
ISMEMBEROF(String)
Example:
IF ISMEMBEROF('domain.lan\Annual_Sales') THEN "Annual_Sales" ELSE "Other" END
USERDOMAIN
The USERDOMAIN function is used to return the current user's domain when the user is signed on to Tableau Server. The Syntax for the USERDOMAIN function is shown below.
Syntax:
USERDOMAIN()
Example:
[Manager]=USERNAME() AND [Domain]=USERDOMAIN()
FULLNAME
The FULLNAME function is used to returns the full name for the current user. The Syntax for FULLNAME is shown below.
Syntax:
FULLNAME( )
Example:
[Manager]=FULLNAME( )
ISFULLNAME
The ISFULLNAME function is used to return true if the present user's name gets a match to the specified full name or false if it does not match. The Syntax for ISFULLNAME function is shown below.
Syntax:
ISFULLNAME(String)
Example:
ISFULLNAME("Charles")
With that, we have finished User Functions. Moving ahead, we will learn Logical Functions.
Logical Functions
The Logical operations on the Data in Tableau are handled by the Logical Functions(One of the Tableau Functions). Following is the list of logical functions in Tableau.
IF
The IF function is implemented to test a series of expressions returning the <then> value for the first true <expr>. The Syntax for the IF function is shown below.
Syntax:
IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2> statement ] [ELSE <else>] END
ELSE
The ELSE function is used to test a series of expressions returning the <then> value for the first true <expr>. The Synta for the ELSE function is shown below.
Syntax:
IF <expr> THEN <then> ELSE <else> END
ELSEIF
The ELSEIF function is used to test a series of expressions returning the <then> value for the first true <expr>. The Syntax for the ELSEIF function is shown below.
Syntax:
IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END
CASE
The CASE function is used to perform logical tests and returns appropriate values. The CASE function evaluates an expression, compares it to a sequence of values, value1, value2, etc., and returns a result. The Syntax of the CASE function is shown below.
Syntax:
CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END
AND
The AND function is used to perform logical conjunction between two expressions. The Syntax for AND function is as follows.
Syntax:
IF <expr1> AND <expr2> THEN <then> END
OR
The OR function is used to perform a disjunction operation between two expressions. The Syntax for the OR function is shown as follows.
Syntax:
IF <expr1> OR <expr2> THEN <then> END
NOT
The NOT function is used to find the logical negation of a given expression. The Syntax for the NOT function is as follows.
Syntax:
IF NOT <expr> THEN <then> END
THEN
The THEN function is used to test a series of expressions returning the <then> value for the first true <expr>. The Syntax of the THEN function is shown below.
Syntax:
IF <expre> THEN <then> [ELSEIF ,expr2> THEN <then2>...] [ELSE <else>] END
WHEN
The WHEN function is used to find the first <value> that matches <expr> and returns the corresponding <return>. The Syntax for the WHEN function is shown below.
Syntax:
CASE <expr> WHEN <Value1> THEN <return1> ... [ELSE <else>] END
END
The END function is used to test a series of expressions returning the <then> value for the first true <expr>. Must be placed at the end of an expression. The Syntax for the END function is shown below.
Syntax:
IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END
With that, we have finished Logical Functions. Moving ahead, we will learn about Aggregate Functions.
Aggregate Functions
The Mathematical computations in Tableau are handled by Aggregate Functions. Following is the list of Aggregate functions in Tableau:
VAR
The VAR function is used to return all values' statistical variances in the given expression based on a population sample.
Syntax:
VAR(expression)
VARP
The VARP function is used to return all values' statistical variance in the entire population's given expression.
Syntax:
VARP(expression)
SUM
The SUM function is implemented to return the sum of all values in the expression. The numeric fields use the SUM function by ignoring the Null values. The Syntax to SUM function is shown below.
Syntax:
SUM(Expression)
STDEV
The STDEV function is used to return the statistical standard deviation of all values in the given expression based on a population sample. The Syntax of the STDEV function is written below.
Syntax:
STDEV(expression)
STDEVP
The STDEVP function is used to return the statistical standard deviation of all values in the given expression based on a biased population. The Syntax of the STDEVP function is written below.
Syntax:
STDEVP(expression)
PERCENTILE
The Percentile function is used to return the percentile value from the given expression corresponding to the specified number. The Syntax of the PERCENTILE function is shown below.
Syntax:
PERCENTILE(expression, number)
MIN
The MIN function is implemented to return the minimum of an expression across all records. The Syntax of the MIN function is shown below.
Syntax:
MIN(expression)
MEDIAN
The MEDIAN function is implemented to return the median of an expression across all records. The Syntax of the MEDIAN function is shown below.
Syntax:
MEDIAN(Expression)
MAX
The MAX function is implemented to return the maximum of an expression across all records. The Syntax of the MAX function is shown below.
Syntax:
MAX(EXPRESSION)
COVARP
The COVARP function is used to return the population covariance of two expressions. The Syntax of the COVARP function is shown below.
Syntax:
COVARP(expression 1, expression2)
COUNT
The COUNT function is implemented to return the number of items in a group. Null values are not ignored. The Syntax for the COUNT function is shown below.
Syntax:
COUNT(Expression)
COUNTD
The COUNTD function is used to return the number of distinct items in a group. The Syntax for the COUNTD function is shown below.
Syntax:
COUNTD(expression)
CORR
The CORR function is used to return the Pearson correlation coefficient of two expressions. The Syntax for the CORR function is written below.
Syntax:
CORR(expression a, expression b)
COLLECT
The COLLECT function is used to combine the values in the argument field and ignoring the Null values. The Syntax for the COLLECT function is shown below.
Syntax:
COLLECT (spatial)
AVG
The AVG function is implemented to return the average of all the values in the expression. The Syntax for the AVG function is shown below.
Syntax:
AVG(Expression)
ATTR
The ATTR function is used to return the expression's value if it has a single value for all rows. The Syntax for the ATTR function is shown below.
Syntax:
ATTR(expression)
With that, we have reached the next segment, where we will learn the Spatial Functions.
Spatial Functions
We execute advanced Spatial Analytics in Tableau using the Spatial Functions in Tableau. The following is the list of Spatial Functions in Tableau
DISTANCE
The DISTANCE function is used to return distance measurement between two points in a specified unit. The Syntax for the DISTANCE function is shown below.
Syntax:
DISTANCE(<geometry1>,<geometry2>,"<units>")
Example:
DISTANCE([Origin MakePoint],[Destination MakePoint], "km")
MAKELINE
The MAKELINE function is used to generate a line mark between two points, useful for building origin-destination maps. The Syntax for the MAKELINE function is shown below.
Syntax:
MAKELINE(<geometry1>,<geometry2>)
Example:
MAKELINE(Origin, Destination)
MAKEPOINT
The MAKEPOINT function is used to convert data from latitude and longitude columns into spatial objects. The Syntax for MAKEPOINT function is shown below.
Syntax:
MAKEPOINT(<latitude>, <longitude>)
Example:
MAKEPOINT([SeaportLatitude],[SeaportLongitude])
MAKEPOINT(X, Y, SRID)
The MAKEPOINT(X, Y, SRID) function is used to convert data from projected geographic coordinates into spatial objects. SRID is a spatial reference identifier that uses ESPG reference system codes to specify coordinate systems. The Syntax for MAKEPOINT(X, Y, SIRD) is mentioned below.
Syntax:
MAKEPOINT(<xcoordinate>, <ycoordinate>, <SRID>
Example:
MAKEPOINT([Xcoord],[Ycoord],2211)
So with that, we have reached the end of this "Tableau Functions: Your One-Stop solution for all the functions in Tableau," article.
Get broad exposure to key technologies and skills used in data analytics and data science, including statistics with the Post Graduate Program in Data Analytics.
Next Steps
Tableau Charts can be your next stop. Tableau Charts will help you represent your data in a user-friendly and readable way.
The link to your next stage is here, Tableau Charts.
Suppose you are looking to enhance your skills and gain in-depth knowledge about Tableau Software to become certified as a Business Intelligence Professional. Feel free to explore Simplilearn's Tableau training and certification program. Designed by experts and delivered by practitioners, this program could be what you are looking forward to learning.
If you have any questions about this "Tableau Functions: Your One-Stop solution for all the functions in Tableau" article, please leave them in the comments section towards the end of this article, and our expert team will answer them; for you at the earliest!
