Functions in Tableau and How to Use Them
A key factor in Tableau’s success is the software’s functions. Analytics demonstrates that Tableau is more than just a nice graphing tool. Data can now be comprehended, analyzed, organized, and reported upon with unmatched efficiency thanks to Tableau Functions. There are many ways to edit data with Tableau, and this blog is dedicated to showing you how to use Functions in Tableau.
Number Functions in Tableau
When the user would need to apply some mathematical operations in Tableau fields, Tableau provides those options. The number functions are one of the Tableau Functions, and they come in handy in situations like these. The following is a list of the number of functions that can be used in Tableau:
ABS
Returning the value’s absolute worth is the job of the ABS Tableau Functions, which are utilized to do just that. Here is how the syntax for the ABS function looks.
Syntax: ABS (Numerical Value)
Example: ABS(-21) = 1
ACOS
Tableau functions known as ACOS are what is used to calculate and return the arc cosine value of the integer that is given. Radian is frequently the unit of measurement that is returned for the results. The following is an example of the syntax for the ACOS Function.
Syntax: ACOS (Numerical Value)
Eg: ACOS(0.5) = 1.0472
ASIN
Returning the arc sine of a given integer is accomplished with the use of the ASIN table functions. Radian is frequently the unit of measurement that is returned for the results. The following is an example of the correct syntax for the ASIN function.
Syntax: ASIN (Numerical Value)
Eg: ASIN(0.5) = 0.5235
ATAN
Tableau‘s ATAN function can be used to return the arc tangent value of a number that has been provided by the user. Radian is frequently the unit of measurement that is returned for the results. The following is an example of the correct syntax for the ATAN Function.
Syntax: ATAN(number)
Eg: ATAN(180) = 1.565
ATAN2
Returning the arc tangent of two numbers that are provided is the purpose of the ATAN2 Tableau Function (x and y). Radians is typically the unit of measurement that is returned for the results. The following is an example of the correct syntax for the ATAN2 function:
Syntax: ATAN2 (x-coordinate, y-coordinate)
Eg: ATAN2 (5,3) = 0.4
CEILING
Rounding up float or double numbers can be accomplished with the use of the CEILING Tableau Function. The syntax for the CEILING function can be seen in the following example.
Syntax: CEILING (Numerical Value)
Eg: CEILING(2.24) = 3
COS
To determine the COSINE value of a specific angle, you can make use of the COS Tableau Functions. The answer is presented to the user in the form of Radians. The syntax for the COS function is presented in the following example.
Syntax: COS (Angle Value)
Eg: COS(PI()/4 = 0.707)
COT
Returning the COTANGENT Value of the specified angle is the responsibility of the COT Tableau Functions. The syntax for the COT function is presented here for your reference.
Syntax: COT (Angle Value)
Eg: COT(PI() /4) = 1
Tableau‘s DEGREES Function can be used to determine the value of Radians in terms of its Degrees. The syntax for the DEGREES function can be seen in the following example.
Syntax:DEGREES (Radian Value)
Eg:DEGREES(PI()/4) = 45.0
DIV
When performing a division Function in Tableau, you can use the DIV Function to retrieve the integer result of the operation by passing the first integer as an argument to the function, and then using the result to divide the second integer by the first integer. The following is an example of the correct syntax for the DIV function:
Syntax: DIV (First Integer, Second Integer)E
Eg: DIV(20,2) = 10
EXP
The EXP function is what is used to determine the number’s exponential value, which can then be used elsewhere. The syntax for the EXP function is displayed below for your convenience.
Syntax: EXP (Numerical Value)
Eg: EXP(2) = 7.389
FLOOR
Tableau methods called FLOOR are used to round a number to the nearest integer that has a value that is equal to or lower than the original number. The syntax for the FLOOR function can be seen in the following example.
Syntax: FLOOR(Numerical Value) Eg: FLOOR(3.234) = 3
SQUARE
The SQUARE function in Tableau is used to determine the SQUARE of the numerical value that has been provided. The following example demonstrates the syntax for the SQUARE function:
Syntax: SQUARE (Numerical Value)
Eg: SQUARE(7) = 49
ZN
Tableau’s ZN function is called whenever the user wants the expression to be returned regardless of whether or not it is null. Otherwise, it returns zero. You can substitute zero values for the null value by using this function. The following is an example of the syntax for the ZN function:
Syntax: ZN(expression)
Eg: ZN(SUM(Sales)) = 73,85,891
After covering the numerical functions, the next topic on the agenda is Tableau’s string functions, which we will examine in the following section.
String Functions
Tableau’s character type data can be manipulated with the help of something called a String Function, which is one of the Tableau Functions. The Tableau String Functions are listed below for your reference.
LOWER
Tableau’s LOWER function is what’s utilized to change a provided upper case string into the lower case version of itself. Below is an illustration of the syntax for the LOWER function.
Syntax: LOWER(string)
Eg: LOWER(TABLEAU) = tableau
UPPER
The Lower Function is used to return the given string in lower case, and the Upper Function is used to return the string in upper case. The following is an example of the correct syntax for the UPPER function.
Syntax: sUPPER(string)
Eg: UPPER(tableau) = TABLEAU
Returns the ASCII code for the first character in the string When the ASCII function is called. The following example demonstrates the syntax for the ASCII function:
Syntax: ASCII (String Value)
Eg: ASCII(‘A’) = 65
CHAR
When an ASCII value is provided, the CHAR function is called upon to return the character in question. The syntax for the CHAR function is presented in the following example:
Syntax: CHAR (ASCII Value)
Eg: CHAR(65) = ‘A’
CONTAINS
Tableau routines known as CONTAINS can be used to determine whether or not a particular substring exists within a string. The syntax for the CONTAINS function can be seen in the following example.
Syntax: CONTAINS (String Value)
Eg: CONTAINS (SOFTLOGIC, LEARN)
ENDSWITH
When invoked, the ENDSWITH method will return the value true if the supplied substring is found at the end of the given String. ENDSWITH will not take into account any trailing white spaces. The syntax is presented in the following format:
Syntax: ENDSWITH (string, substring)
Eg: ENDSWITH(“Softlogic”, “earn”) = true
FIND
If the substring is not discovered, the FIND function will return null. Otherwise, it will return the index position of the substring within the string. Any substring instances that occur before the index position starts are disregarded by the function if an additional argument start is supplied. The index number that corresponds to the first character in the string is 1. The following is an example of the syntax for FIND:
Syntax: FIND (string, substring, [start])
E FIND(“ABCD”, “CD”) = 2
LEFT
The LEFT function is what is used to return the number of characters that are the furthest to the left in the String. The syntax for the LEFT function is presented in the following example.
Syntax: LEFT (String, number)
Eg: LEFT(“Softlogic“, 5) = “Softl”
RIGHT
The RIGHT function is what’s utilized to get the number of characters that are furthest to the right in the String. The syntax for the RIGHT function is presented in the following example.
Syntax: RIGHT (String, number)
RIGHT(“Softlogic“, ) = “logic”
LEN
The LEN function is designed to return the total length of the string that has been passed into it. The syntax for the LEN function is shown here for your reference.
Syntax: LEN (String Value)
Eg: LEN(“Softlogic“) = 9
TRIM
The string is returned by the TRIM function after the leading and trailing spaces have been removed, as well as any other spaces that may have been present. The syntax of TRIM is presented here for your reference.
Syntax: TRIM(String)
Eg: TRIM(” Softlogic “) = “S”
LTRIM
After removing any leading spaces, the String is what is returned by the LTRIM function after it has been invoked. The syntax for the LTRIM function can be found in the following example.
Syntax: LTRIM(String)
Eg: LTRIM(” Softlogic “) = “S”
MAX
The MAX function is designed to return the value that is greater than both a and b together. The following is an example of correct syntax for the MAX function.
Syntax: MAX(a,b)
Example: MAX (“abc”,”pqrs”) = “pqrs”
>
MID
It is possible to retrieve the String beginning at the starting point by calling the MID function. Position 1 refers to the character that comes first in the string. In the event that an additional argument length is provided, the String that is delivered has exactly that many characters. The syntax for the MID function can be seen in the following example.
Syntax: (MID (string, start, [length])
Example:MID(“Softlogic“, 6) = “logic”
REPLACE
The REPLACE function searches the String for the substring and then replaces it with the replacement string. This is done by searching the String using the REPLACE function. The syntax is displayed below here.
Syntax: REPLACE (string, substring, replacement)
Eg: REPLACE(“Java 8”, “8”, “13”) = “Java 13”
RTRIM
When you use the RTRIM function, you can get back a string that has had any trailing spaces removed from it. The syntax of RTRIM is displayed down below for your perusal.
Syntax: RTRIM(string)
Example: RTRIM(” Tableau “) = ” Tableau”
SPACE
When called, the SPACE function will return a string that contains the number of times that the space character has been repeated. The syntax for the SPACE function is presented in the following example.
Syntax: SPACE(Number)
Example: SPACE() = ” “
SPLIT
The SPLIT function is used to return a substring from a string by utilizing a delimiter character to break the string into a tokens sequence. This function is implemented so that it can return a substring from a string. The syntax for the SPLIT function is presented in the following example:
Syntax: SPLIT (String, delimiter, token number)
Eg: SPLIT (‘a-b-c-d’, ‘-‘, 4) = ‘c
RETURNSWITH
If the string you’re working with begins with a substring, you can ask the RETURNSWITH function to return true. There is no consideration given to leading white spaces. The syntax for RETURNSWITH is displayed below for your reference.
Syntax: STARTSWITH (string, substring)
Eg: STARTSWITH(“Softlogic“, “So”) = true
Now that this is out of the way, we may consider the Tableau String Functions to be finished. Moving on, we will carry on with the discussion of USER Functions in Tableau.
User Functions
The USER function, which is one of the Tableau functions, is responsible for managing the details and methods that are associated with the USER in real time. The following is a list of functions available to users in Tableau:
USERNAME
The name of the currently logged in user can be retrieved by calling the function designated by its name, USERNAME. The syntax for USERNAME can be seen in the following example.
Syntax: USERNAME ( )
Eg: [Manager]=USERNAME( )
ISUSERNAME
The ISUSERNAME function will return true if the username of the current user is the same as the supplied username, and it will return false if the usernames do not match. The syntax for the ISUSERNAME can be seen in the following example.
Syntax: ISUSERNAME(String)
Eg: ISUSERNAME(“Harry”)
ISMEMBEROF
If the user who is now using Tableau is a member of a group whose characteristics match those of the given String, the ISMEMEROF function can be used to return the value true. Following is a description of the syntax used by the ISMEMBEROF function.
Syntax: ISMEMBEROF(String)
For instance, if the expression “IF ISMEMBEROF(‘domain.lanAnnual Sales’) THEN “Annual Sales” AND ELSE “Other” END, then “Annual Sales”
USERDOMAIN
When a user is logged into Tableau Server, the USERDOMAIN function is used to return the user’s domain that is currently being used. Below is an example of the correct syntax to use when calling the USERDOMAIN function.
Syntax: USERDOMAIN()
Eg: [Manager]=USERNAME() AND [Domain]=USERDOMAIN()
FULLNAME
The FULLNAME function is what you need to use if you want to find out the full name of the currently logged-in user. Below is an illustration of the syntax for FULLNAME.
Syntax: FULLNAME ( )
Eg: FULLNAME ( ) [Manager]=FULLNAME( )
ISFULLNAME
The ISFULLNAME method is used to return true if the name of the current user gets match to the full name that is supplied, and it returns false if the name does not receive a match. The syntax for the ISFULLNAME function is displayed below for your convenience.
Syntax: ISFULLNAME(String)
Eg: ISFULLNAME(“Charles”)
Having accomplished that, we are now completed with the User Functions. As we go, the next topic on the agenda is logical functions.
Logical Functions in Tableau
Logical functions in Tableau are responsible for performing any logical operations required on the data (One of the Tableau Functions). The list of Tableau’s logical operations is provided below.
IF
The IF function is used to test a list of expressions and returns the value for the first true expression <expr>. The IF function’s syntax is displayed below.
Syntax:IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2> statement ] [ELSE <else>] END
ELSE
A succession of expressions are tested using the ELSE function, which returns the then> value for the first true expr>. The ELSE function’s Syntax is displayed below.
Syntax:IF <expr> THEN <then> ELSE <else> END
ELSEIF
A succession of expressions are tested using the ELSEIF function, which returns the then> value for the first true expr>. The ELSEIF function’s syntax is displayed below.
Syntax:IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>…] [ELSE <else>] END
CASE
The CASE function is employed to carry out logical tests and provides suitable results. A result is returned by the CASE function after it evaluates an expression and compares it to a series of values, such as value1, value2, etc. The CASE function’s syntax is displayed below.
Syntax :CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> … ELSE <default return> END
AND
To execute logical conjunction between two expressions, utilize the AND function. The AND function has the following syntax.
Syntax:IF <expr1> AND <expr2> THEN <then> END
OR
The disjunction operation between two phrases is carried out using the OR function. The syntax for the OR function is expressed here
Syntax:IF <expr1> OR <expr2> THEN <then> END
NOT
To determine the logical negation of a given expression, use the NOT function. The NOT function syntax is written below.
Syntax:IF NOT <expr> THEN <then> END
THEN
A succession of expressions are tested using the THEN function, which returns the then> value for the first true expr>. The THEN function’s syntax is displayed below.
Syntax :IF <expre> THEN <then> [ELSEIF ,expr2> THEN <then2>…] [ELSE <else>] END
WHEN
The first “value” that matches “expr” is located using the WHEN function, which then returns the corresponding “return”. The WHEN function’s syntax is displayed below.
Syntax :CASE <expr> WHEN <Value1> THEN <return1> … [ELSE <else>] END
END
The then> value for the first true expr> is returned by the END function, which is used to test a number of expressions. It has to come after an expression. The END function’s syntax is displayed below.
IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>…] [ELSE <else>] END
We have now finished discussing logical functions. We shall learn about aggregate functions later on.
Aggregate Function in Tableau
Aggregate Functions are responsible for handling the mathematical operations in Tableau. The list of Tableau’s aggregate functions is as follows:
VAR
The specified expression’s statistical variances for each value are returned using the VAR model that focuses on a sample population.
Syntax: VAR(expression)
VARP
The VARP function is used to return the statistical variance for each value across the entire population.
Syntax:VARP(expression)
SUM
To return the total of all the values in the expression, the SUM function is implemented. By disregarding Null values, the numerical fields employ the SUM function. The function Syntax to SUM is displayed below.
Syntax:SUM(Expression)
STDEV
The STDEV function is used to return, based on a population sample, the statistical standard deviation of each value in the given expression. The STDEV function’s syntax is listed below.
Syntax:STDEV(expression)
STDEVP
By using a skewed population, the STDEVP function returns the statistical standard deviation of each value in the given expression. The STDEVP function’s syntax is listed below.
Syntax: STDEVP(expression)
PERCENTILE
The percentile value from the given expression that corresponds to the given integer is returned by the Percentile function. The PERCENTILE function’s syntax is displayed below.
Syntax: PERCENTILE(expression, number)
MIN
The MIN function is used to return an expression’s minimum value across all records. The MIN function’s syntax is displayed below.
Syntax: MIN(expression)
MEDIAN
The MEDIAN function is used to return an expression’s median value over all records. The MEDIAN function’s syntax is displayed below.
Syntax:MEDIAN(Expression)
MAX
The MAX function is used to return an expression’s maximum value across all records. The MAX function’s syntax is displayed below.
Syntax: MAX(EXPRESSION)
COVARP
The population covariance of two expressions is returned by the COVARP function. The COVARP function’s syntax is displayed below.
Syntax:
COVARP(expression 1, expression2)
COUNT
To determine how many items are in a group, the COUNT function is used. Not being ignored are null values. The COUNT function’s syntax is displayed below.
Syntax: COUNT(Expression)
COUNTD
The number of unique elements in a group is returned by the COUNTD function. The COUNTD function’s syntax is displayed below.
Syntax: COUNTD(Expression)
The Pearson correlation coefficient of two expressions is obtained using the CORR function. The CORR function’s syntax is listed below.
Syntax: CORR(expression a, expression b)
COLLECT
The argument field’s values are combined using the COLLECT function, which ignores Null values. The COLLECT function’s syntax is displayed below.
Syntax: COLLECT (spatial)
AVG
The AVG function is used to return the expression’s overall average value. The AVG function’s syntax is displayed below.
Syntax:
AVG(Expression)
ATTR
If the expression only has one value for every row, the ATTR function is used to return the expression’s value. The ATTR function’s syntax is displayed below.
We’ve now reached the section where we’ll learn about spatial functions.
Spatial Features
Using Tableau’s spatial functions, we carry out sophisticated spatial analytics. The list of spatial functions in Tableau is provided below.
DISTANCE
The DISTANCE function is used to return the distance in units provided between two places. Below is a syntax diagram for the DISTANCE function.
Syntax :
DISTANCE(<geometry1>,<geometry2>,”<units>”)
Example:
DISTANCE([Origin MakePoint],[Destination MakePoint], “km”)
MAKELINE
When creating origin-destination maps, the MAKELINE function is used to create a line mark between two points. The MAKELINE function’s syntax is displayed below.
Syntax: sMAKELINE
(<geometry1>,<geometry2>)
as in MAKELINE (Origin, Destination)
MAKEPOINT
Spatial objects are created from latitude and longitude data using the MAKEPOINT function. Below is a syntax diagram for the MAKEPOINT function.
Syntax: MAKELINE
(<geometry1>,<geometry2>)
Example:
MAKELINE(Origin, Destination)
MAKEPOINT
To transform information from projected geographic coordinates into spatial objects, use the MAKEPOINT(X, Y, SRID) function. A spatial reference identifier called SRID specifies coordinate systems using ESPG reference system codes. Below is a description of the syntax for MAKEPOINT(X, Y, SIRD).
Syntax: MAKEPOINT(<xcoordinate>, <ycoordinate>, <SRID>
Example :
MAKEPOINT([Xcoord],[Ycoord],2211)
The section about Tableau Functions serves as your One-Stop option to learn all the functions in Tableau.
