Tableau is a tool that is not just meant for pretty graphs. Functions in Tableau is crucial for optimum Data Representation and hence, it is a staple concept across all Tableau Certification Curriculum.
Thankfully, this tool has various categories of built-in functions that you can directly apply to your uploaded data. If you’ve used MS Excel or SQL, these should seem pretty familiar to you.
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(number)
ABS(-4) = 4
2. ACOS
This function returns the arc cosine of the given number in Radians.
Syntax
ACOS(number)
ACOS(-1) = 3.14159265358979
3. ASIN
This function returns the arc sine of the given number in Radians.
Syntax
ASIN(number)
ASIN(1) = 1.5707963267949
4. ATAN
This function returns the arc tangent of the given number in Radians.
Syntax
ATAN(number)
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(number)
CEILING(3.1415) = 4
6. COS
This function returns the cosine of the given angle specified in Radians.
Syntax
COS(number)
COS(PI()/4) = 0.707106781186548
7. COT
This function returns the cotangent of the given angle specified in Radians.
Syntax
COT(number)
CO1(PI()/4) = 1
8. DEGREES
This function returns the value of the given angle in Degrees.
Syntax
DEGREES(number)
DEGREES(PI()/4) = 45
9. DIV
This function returns the integer value of the quotient, given the Dividend and Divisor.
Syntax
DIV(integer1, integer2)
DIV(11,2) = 5
10. EXP
This function returns the value of e raised to the power of the given number.
Syntax
EXP(number)
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(number)
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(number, number)
HEXBINX([Longitude], [Latitude])
13. LN
This function returns the natural log of the given number.
Syntax
LN(number)
LN(1) = 0
14. LOG
This function returns the log with base 10 of the given number.
Syntax
LOG(number, [base])
LOG(1) = 0
15. MAX
This function returns the maximum of the passed arguments.
Syntax
MAX(number, number)
MAX(4,7) = 7MAX(Sales,Profit)
16. MIN
This function returns the minimum of the passed arguments.
Syntax
MIN(number, number)
MIN(4,7) = 4MIN(Sales,Profit)
17. PI
This function returns the value of Pi.
Syntax
PI() = 3.142
18. POWER
This function returns the value of the first argument raised to the power of the second argument.
Syntax
POWER(number, power)
POWER(2,10) = 1024
19. RADIANS
This function returns the value of the given angle in Radians.
Syntax
RADIANS(number)
RADIANS(45) = 0.785397
20. ROUND
This function returns the given number rounded off to the specified number of decimal places.
Syntax
ROUND(number, [decimal place])
ROUND([Profit])
21. SIGN
This function returns the sign of a given number.
Syntax
SIGN(number)
SIGN(AVG(Profit)) = -1
22. SIN
This function returns the sine of the given angle specified in Radians.
Syntax
SIN(number)
SIN(PI()/4) = 0.707106781186548
23. SQRT
This function returns the square root of the given number.
Syntax
SQRT(number)
SQRT(25) = 5
24. SQUARE
This function returns the square of the given number.
Syntax
SQUARE(number)
SQUARE(5) = 25
25. TAN
This function returns the tangent of the given angle specified in Radians.
Syntax
TAN(number)
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(string)
ASCII('A') = 65
2. CHAR
This function returns the character represented by the ASCII code.
Syntax
CHAR(ASCII code)
CHAR(65) = 'A'
3. CONTAINS
If the string contains said substring, this function returns true.
Syntax
CONTAINS(string, substring)
CONTAINS(“Edureka”, “reka”) = true
4. ENDSWITH
Given the string ends with said substring, this function returns true.
Syntax
ENDSWITH(string, substring)
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(string, substring, [start])
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
FINDNTH(string, substring, occurrence)
FIND(“Edureka”, “e”, 2) = 5
7. LEFT
This function returns the left-most number of characters in the given string.
Syntax
LEFT(string, number)
LEFT(“Edureka”, 3) = "Edu"
8. LEN
This function returns the length of the given string.
Syntax
LEN(string)
LEN(“Edureka”) = 7
9. LOWER
This function returns the entire given string in lowercase alphabets.
Syntax
LOWER(string)
LOWER(“Edureka”) = edureka
10. LTRIM
This function returns the given string without any preceding space.
Syntax
LTRIM(string)
LTRIM(“ Edureka ”) = "Edureka "
11. MAX
This function returns the maximum of the two passed string arguments.
Syntax
MAX(a, b)
MAX ("Apple","Banana") = "Banana"
12. MID
This function returns the given string from the index position of start.
Syntax
MID(string, start, [length])
MID("Edureka", 3) = "reka"
13. MIN
This function returns the minimum of the two passed string arguments.
Syntax
MIN(a, b)
MIN ("Apple","Banana") = "Apple"
14. REPLACE
This function searches the given string for the substring and replaces it with the replacement.
Syntax
REPLACE(string, substring, replacement)
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(string, number)
RIGHT(“Edureka”, 3) = "eka"
16. RTRIM
This function returns the given string without any succeeding space.
Syntax
RTRIM(string)
RTRIM(“ Edureka ”) = " Edureka"
17. SPACE
This function returns a string consisting of a specified number of spaces.
Syntax
SPACE(number)
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(string, delimiter, token number)
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(string, substring)
STARTSWITH(“Edureka”, “Edu”) = true
20. TRIM
This function returns the given string without any preceding or succeeding space.
Syntax
TRIM(string)
TRIM(“ Edureka ”) = "Edureka"
21. UPPER
This function returns the entire given string in uppercase alphabets.
Syntax
UPPER(string)
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(date_part, interval, date)
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
DATEDIFF(date_part, date1, date2, [start_of_week])
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(date_part, date, [start_of_week])
DATENAME('month', #2019-12-17#) = December
4. DATEPART
This function returns the date part of the date in integer form.
Syntax
DATEPART(date_part, date, [start_of_week])
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(date_part, date, [start_of_week])
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(Date)
DAY(#2019-12-17#) = 17
7. ISDATE
Given a string is a valid date, this function returns true.
Syntax
ISDATE(String)
ISDATE(December 17, 2019) = true
8. MAKEDATE
This function returns the date value constructed from the specified year, month, and date.
Syntax
MAKEDATE(year, month, day)
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(date, time)
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(hour, minute, second)
MAKETIME(11, 28, 28) = #11:28:28#
11. MONTH
This function returns the month of the given date in integer form.
Syntax
MONTH(Date)
MONTH(#2019-12-17#) = 12
12. NOW
This function returns the current date and time.
Syntax
NOW()
NOW() = 2019-12-17 11:28:28 PM
13. TODAY
This function returns the current date.
Syntax
TODAY()
TODAY() = 2019-12-17
14. YEAR
This function returns the year of the given date in integer form.
Syntax
YEAR(Date)
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(expression)
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(expression)
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(format, string)
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(expression)
FLOAT(3) = 3.000FLOAT([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(expression)
INT(8.0/3.0) = 2INT(4.0/1.5) = 2INT(-9.7) = -9
6. STRING
This function is used to cast its argument as a string.
Syntax
STR(expression)
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
ATTR(expression)
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
AVG(expression)
3. COLLECT
This is an aggregate calculation which combines the values in the argument field ignoring the null values.
Syntax
COLLECT(Spatial)
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
CORR(expr1, expr2)
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
COUNT(expression)
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
COUNTD(expression)
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
COVAR(expr1, EXPR2)
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
COVARP(expr1, EXPR2)
9. MAX
This function returns the maximum of an expression across all records, ignoring NULL values.
Syntax
MAX(expression)
10. MEDIAN
This function returns the median of an expression across all records, ignoring NULL values.
Syntax
MEDIAN(expression)
11. MIN
This function returns the minimum of an expression across all records, ignoring NULL values.
Syntax
MIN(expression)
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
PERCENTILE(expression, number)
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
STDEV(expression)
14. STDEVP
This function in Tableau returns the statistical Standard Deviation of all values in the given expression based on the biased population.
Syntax
STDEVP(expression)
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
SUM(expression)
16. VAR
Given expression based on a sample of the population, this function returns the statistical variance of all values.
Syntax
VAR(expression)
17. VARP
Given expression based on the entire population, this function returns the statistical variance of all values.
Syntax
VARP(expression)
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 <expr1> AND <expr2> THEN <then> END
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 <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END
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 <expr> THEN <then> ELSE <else> END
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 ESLEIF condition.
Syntax
IF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] ELSE <else> END
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 <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] ELSE <else> END
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(expr1, expr2)
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(test, then, else, [unknown])
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(String)
ISDATE("2004-04-15") = True
9. ISNULL
This function checks if a given expression contains valid data and if so, returns true.
Syntax
ISNULL(expression)
ISNULL ([Profit])
10. NOT
This function performs logical NOT (negation) on given expression.
Syntax
IF NOT <expr> THEN <then> END
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 <expr1> OR <expr2> THEN <then> END
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 <expr> WHEN <Value1> THEN <return1> ... [ELSE <else>] END
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(expression)
ZN([Profit])
These were all the essential Functions in Tableau to learn more about Tableau and the various concepts associated with it, you could check out this playlist. Elevate your preparation and ace your Tableau developer interview with our expertly curated collection of Tableau interview questions.
If you wish to master Tableau, Edureka has a curated course on Tableau Training Certification which covers various concepts of data visualization in depth, including conditional formatting, scripting, linking charts, dashboard integration, Tableau integration with R and more.