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

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 AND 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 ``WHEN THEN ``WHEN THEN ... ``ELSE ``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 THEN 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 THEN [ELSEIF THEN ...] 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 THEN [ELSEIF THEN ...] 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 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 OR 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 WHEN THEN ... [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.

The post Functions in Tableau and How to Use Them appeared first on Edureka.