Functions in Tableau and How to Use Them

Tableau is a tool which 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 Curricula. 

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. 

So, the following are the various categories of functions that we’ll discuss through this blog.

• Number Functions
• String Functions
• Date Functions
• Type Conversion Functions
• Aggregate Functions
• Logical Functions

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) = 7
MAX(Sales,Profit) 

16. MIN

This function returns the minimum of the passed arguments.

Syntax

MIN(number, number)

MIN(4,7) = 4
MIN(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.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(expression)

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(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.

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.