List of Tableau Functions you can use/try
Number Functions
These built-in functions in Tableau allow you to perform computations on the data values in your fields. Number functions can only be used with fields that contain numerical values. Following are the various Number Functions in Tableau:
1. ABS
This function returns the absolute value of the number given.
Syntax :-
ABS(-4) = 4
2. ACOS
This function returns the arc cosine of the given number in Radians.
Syntax :-
ACOS(-1) = 3.14159265358979
3. ASIN
This function returns the arc sine of the given number in Radians.
Syntax :-
ASIN(1) = 1.5707963267949
4. ATAN
This function returns the arc tangent of the given number in Radians.
Syntax :-
ATAN(180) = 1.5652408283942
5. CEILING
This function returns the given number rounded off to the nearest integer of equal or greater value.
Syntax :-
CEILING(3.1415) = 4
6. COS
This function returns the cosine of the given angle specified in Radians.
Syntax :-
COS(PI()/4) = 0.707106781186548
7. COT
This function returns the cotangent of the given angle specified in Radians.
Syntax :-
CO1(PI()/4) = 1
8. DEGREES
This function returns the value of the given angle in Degrees.
Syntax :-
DEGREES(PI()/4) = 45
9. DIV
This function returns the integer value of the quotient, given the Dividend and Divisor.
Syntax :-
DIV(11,2) = 5
10. EXP
This function returns the value of e raised to the power of the given number.
Syntax :-
EXP(2) = 7.389
EXP(-[Growth Rate]*[Time])
11. FLOOR
This function returns the given number rounded off to the nearest integer of equal or lesser value.
Syntax :-
FLOOR(6.1415) = 6
12. HEXBIN X,Y
HEXBINX and HEXBINY are binning and plotting functions for hexagonal bins. This function Maps an x, y coordinate to the x-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.
Syntax :-
HEXBINX([Longitude], [Latitude])
13. LN
This function returns the natural log of the given number.
Syntax :-
LN(1) = 0
14. LOG
This function returns the log with base 10 of the given number.
Syntax :-
LOG(1) = 0
15. MAX
This function returns the maximum of the passed arguments.
Syntax :-
MAX(4,7) = 7
16. MIN
This function returns the minimum of the passed arguments.
Syntax :-
MIN(4,7) = 4
MIN(Sales, Profit)
17. PI
This function returns the value of Pi.
Syntax :-
18. POWER
This function returns the value of the first argument raised to the power of the second argument.
Syntax :-
POWER(2,10) = 1024
19. RADIANS
This function returns the value of the given angle in Radians.
Syntax :-
RADIANS(45) = 0.785397
20. ROUND
This function returns the given number rounded off to the specified number of decimal places.
Syntax :-
ROUND([Profit])
21. SIGN
This function returns the sign of a given number.
Syntax :-
SIGN(AVG(Profit)) = -1
22. SIN
This function returns the sine of the given angle specified in Radians.
Syntax :-
SIN(PI()/4) = 0.707106781186548
23. SQRT
This function returns the square root of the given number.
Syntax :-
SQRT(25) = 5
24. SQUARE
This function returns the square of the given number.
Syntax :-
SQUARE(5) = 25
25. TAN
This function returns the tangent of the given angle specified in Radians.
Syntax :-
TAN(PI()/4) = 1
String Functions
These built-in functions in Tableau allow you to manipulate string data. You can do things like pull all the last names from all your customers into a new field using these functions. Following are the various String Functions in Tableau:
1. ASCII
This function returns the ASCII code for the first character of the said string.
Syntax :
ASCII(‘A’) = 65
2. CHAR
This function returns the character represented by the ASCII code.
Syntax :
CHAR(65) = ‘A’
3. CONTAINS
If the string contains said substring, this function returns true.
Syntax :
CONTAINS(“Edureka”, “reka”) = true
4. ENDSWITH
Given the string ends with said substring, this function returns true.
Syntax :
ENDSWITH(“Edureka”, “reka”) = true
5. FIND
If the string contains said substring, this function returns the index position of the substring in the string, else 0. If the optional argument start is added, the function ignores any instances of the substring that appears before the index position start.
Syntax :
FIND(“Edureka”, “reka”) = 4
6. FINDNTH
If the string contains said substring, this function returns the index position of the nth occurrence of the substring in the string.
Syntax :
FIND(“Edureka”, “e”, 2) = 5
7. LEFT
This function returns the left-most number of characters in the given string.
Syntax :
LEFT(“Edureka”, 3) = “Edu”
8. LEN
This function returns the length of the given string.
Syntax :
LEN(“Edureka”) = 7
9. LOWER
This function returns the entire given string in lowercase alphabets.
Syntax :
LOWER(“Edureka”) = edureka
10. LTRIM
This function returns the given string without any preceding space.
Syntax :
LTRIM(“ Edureka ”) = “Edureka “
11. MAX
This function returns the maximum of the two passed string arguments.
Syntax :
MAX (“Apple”,”Banana”) = “Banana”
12. MID
This function returns the given string from the index position of start.
Syntax :
MID(“Edureka”, 3) = “reka”
13. MIN
This function returns the minimum of the two passed string arguments.
Syntax :
MIN (“Apple”,”Banana”) = “Apple”
14. REPLACE
This function searches the given string for the substring and replaces it with the replacement.
Syntax :
REPLACE(“Version8.5”, “8.5”, “9.0”) = “Version9.0”
15. RIGHT
This function returns the right-most number of characters in the given string.
Syntax :
RIGHT(“Edureka”, 3) = “eka”
16. RTRIM
This function returns the given string without any succeeding space.
Syntax :
RTRIM(“ Edureka ”) = ” Edureka”
17. SPACE
This function returns a string consisting of a specified number of spaces.
Syntax :
SPACE(1) = ” “
18. SPLIT
This function returns a substring from a string, using a delimiter character to divide the string into a sequence of tokens.
Syntax :
SPLIT (‘a-b-c-d’, ‘-‘, 2) = ‘b’
SPLIT (‘a|b|c|d’, ‘|‘, -2) = ‘c’
19. STARTSWITH
Given the string starts with said substring, this function returns true.
Syntax :
STARTSWITH(“Edureka”, “Edu”) = true
20. TRIM
This function returns the given string without any preceding or succeeding space.
Syntax :
TRIM(“ Edureka ”) = “Edureka”
21. UPPER
This function returns the entire given string in uppercase alphabets.
Syntax :
UPPER(“Edureka”) = EDUREKA
Date Functions
These built-in functions in Tableau allow you to manipulate Dates in your data source such as year, month, date, day and/or time. Following are the various Date Functions in Tableau:
1. DATEADD
This function returns the specified date with the specified number interval added to the specified date_part of said date.
Syntax :
DATEADD(‘month’, 3, #2019-09-17#) = 2019-12-17 12:00:00 AM
2. DATEDIFF
This function returns the difference between both the dates expressed in units of the date part. The start of the week can be adjusted to the day a user needs to.
Syntax :
DATEDATEDIFF(‘week’, #2019-12-15#, #2019-12-17#, ‘monday’)= 1
3. DATENAME
This function returns the date part of the date in string form.
Syntax :
DATENAME(‘month’, #2019-12-17#) = December
4. DATEPART
This function returns the date part of the date in integer form.
Syntax :
DATEPART(‘month’, #2019-12-17#) = 12
5. DATETRUNC
This function returns the truncated form of the specified date to the accuracy specified by date part. You essentially get returned a new date altogether, through this function.
Syntax :
DATETRUNC(‘quarter’, #2019-12-17#) = 2019-07-01 12:00:00 AM
DATETRUNC(‘month’, #2019-12-17#) = 2019-12-01 12:00:00 AM
6. DAY
This function returns the day of the given date in integer form.
Syntax :
DAY(#2019-12-17#) = 17
7. ISDATE
Given a string is a valid date, this function returns true.
Syntax :
ISDATE(December 17, 2019) = true
8. MAKEDATE
This function returns the date value constructed from the specified year, month, and date.
Syntax :
MAKEDATE(2019, 12, 17) = #December 17, 2019#
9. MAKEDATETIME
This function returns the date and time values constructed from the specified year, month and date and the hour, minute and second.
Syntax :
MAKEDATETIME(“2019-12-17”, #11:28:28PM#) = #12/17/2019 11:28:28 PM#
MAKEDATETIME([Date], [Time]) = #12/17/2019 11:28:28 PM#
10. MAKETIME
This function returns the time value constructed from the specified hour, minute and second.
Syntax :
MAKETIME(11, 28, 28) = #11:28:28#
11. MONTH
This function returns the month of the given date in integer form.
Syntax :
MONTH(#2019-12-17#) = 12
12. NOW
This function returns the current date and time.
Syntax :
NOW() = 2019-12-17 11:28:28 PM
13. TODAY
This function returns the current date.
Syntax :
TODAY() = 2019-12-17
14. YEAR
This function returns the year of the given date in integer form.
Syntax :
YEAR(#2019-12-17#) = 2019
Type Conversion Functions
These built-in functions in Tableau allow you to convert fields from one data type to another, e.g, you can convert numbers to strings, to prevent or enable aggregation by Tableau. Following are the various Type Conversion Functions in Tableau:
1. DATE
Given a number, string, or date expression, this function returns a date.
Syntax :
DATE([Employee Start Date])
DATE(“December 17, 2019”) = #December 17, 2019#
DATE(#2019-12-17 14:52#) = #2019-12-17#
2. DATETIME
Given a number, string, or date expression, this function returns a date-time.
Syntax :
DATETIME(“December 17, 2019 07:59:00”) = December 17, 2019 07:59:00
3. DATEPARSE
Given a string, this function returns a date-time in the specified format.
Syntax :
DATEPARSE (“dd.MMMM.yyyy”, “17.December.2019”) = #December 17, 2019#
DATEPARSE (“h’h’ m’m’ s’s'”, “11h 5m 3s”) = #11:05:03#
4. FLOAT
This function is used to cast its argument as a floating point number.
Syntax :
FLOAT(3) = 3.000
FLOAT([Salary])
5. INT
This function is used to cast its argument as an integer. For certain expressions, it also truncates results to the nearest integer to zero.
Syntax :
INT(8.0/3.0) = 2
INT(4.0/1.5) = 2
INT(-9.7) = -9
6. STRING
This function is used to cast its argument as a string.
Syntax :
STR([Date])
Aggregate Functions
These built-in functions in Tableau allow you to summarize or change the granularity of your data. Following are the various Aggregate functions in Tableau:
1. ATTR
This function returns the value of the expression if it has a single value for all rows, ignoring the NULL values, else returns an asterisk.
Syntax :
2. AVG
This function returns the mean of all the values in an expression, ignoring the NULL values. AVG can be used with numeric fields only.
Syntax :
3. COLLECT
This is an aggregate calculation which combines the values in the argument field ignoring the null values.
Syntax :
4. CORR
This calculation returns the Pearson correlation coefficient of two expressions. The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of the corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship.
Syntax :
5. COUNT
This is a function used to return the count of items in a group, ignoring the NULL values. Meaning, if there are multiple numbers of the same item, this function will count it as separate items and not a single item.
Syntax :
6. COUNTD
This is a function used to return the distinct count of items in a group, ignoring the NULL values. Meaning, if there are multiple numbers of the same item, this function will count it as a single item.
Syntax :
7. COVAR
This is a function which returns the Sample Covariance of two expressions. The nature of two variables changing, together, can be quantified using Covariance. A positive covariance indicates that the variables tend to move in the same direction, as when the value of one variable tends to grow larger, so does the value of the other. Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population.
Syntax :
8. COVARP
This is a function which returns the Population Covariance of two expressions. Population covariance is the appropriate choice when there is data available for all items of interest for the entire population, not just a sample.
Syntax :
9. MAX
This function returns the maximum of an expression across all records, ignoring NULL values.
Syntax :
10. MEDIAN
This function returns the median of an expression across all records, ignoring NULL values.
Syntax :
11. MIN
This function returns the minimum of an expression across all records, ignoring NULL values.
Syntax :
12. PERCENTILE
This function returns the percentile value of a given expression. This number returned must be between 0 and 1 – for example, 0.34, and must be a numeric constant.
Syntax :
13. STDEV
This function in Tableau returns the statistical Standard Deviation of all values in the given expression based on a sample of the population.
Syntax :
14. STDEVP
This function in Tableau returns the statistical Standard Deviation of all values in the given expression based on the biased population.
Syntax :
15. SUM
This function in Tableau returns the sum of all values in the expression, ignoring the NULL values. SUM can be used with numeric fields only.
Syntax :
16. VAR
Given expression based on a sample of the population, this function returns the statistical variance of all values.
Syntax :
17. VARP
Given expression based on the entire population, this function returns the statistical variance of all values.
Syntax :
Logical Functions
These built-in functions in Tableau allow you to determine if a certain condition is true or false (Boolean logic). Following are the various Logical functions in Tableau:
1. AND
This function performs logical AND (conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.
Syntax :
IF (ATTR([Market]) = “Asia” AND SUM([Sales]) > [Emerging Threshold] )THEN “Well Performing”
2. CASE
This function in Tableau performs logical tests and returns appropriate values, comparable to SWITCH CASE in most common programming languages. When a value that matches condition specified in the given expression, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, this function returns NULL. CASE is often easier to use than IIF or IF THEN ELSE.
Syntax:
CASE [Region] WHEN ‘West’ THEN 1 WHEN ‘East’ THEN 2 ELSE 3 END
3. ELSE & IF, THEN
This function in Tableau tests a series of inputs returning the THEN value for the first expression that fulfills your IF condition.
Syntax :
IF [Profit] > 0 THEN ‘Profit’ ELSE ‘Loss’ END
4. ELSEIF
This function in Tableau tests a series of inputs returning the THEN value for the first expression that fulfills your ELSEIF condition.
Syntax :
IF [Profit] > 0 THEN ‘Profit’ ELSEIF [Profit] = 0 THEN ‘No Profit No Loss’ ELSE ‘Loss’ END
5. END
This function ends an expression.
Syntax :
IF [Profit] > 0 THEN ‘Profit’ ELSEIF [Profit] = 0 THEN ‘No Profit No Loss’ ELSE ‘Loss’ END
6. IFNULL
This Tableau function returns expr1 not NULL, else returns expr2.
Syntax :
IFNULL ([Profit], 0)
7. IIF
This Tableau function checks whether a condition is fulfilled, returns a value if TRUE, another if FALSE, and a third value or NULL if unknown.
Syntax:
IIF([Profit] > 0, ‘Profit’, ‘Loss’, 0)
8. ISDATE
This function checks if a given string is a valid date and if so, returns true.
Syntax:
ISDATE(“2004-04-15”) = True
9. ISNULL
This function checks if a given expression contains valid data and if so, returns true.
Syntax:
ISNULL ([Profit])
10. NOT
This function performs logical NOT (negation) on given expression.
Syntax:
IF NOT [Profit] > 0 THEN “No Profit” END
11. OR
This function performs logical OR (disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.
Syntax:
IF [Profit] < 0 OR [Profit] = 0 THEN “Needs Improvement” END
12. WHEN
This function finds the first value that fulfills the condition in the given expression and returns the corresponding return.
Syntax:
CASE [RomanNumberals] WHEN ‘I’ THEN 1 WHEN ‘II’ THEN 2 ELSE 3 END
13. ZN
This function in Tableau returns the given expression if it is not NULL, else returns zero
Syntax:
ZN([Profit])