Data analysis requires many calculations. The calculation editor in Tableau is used to apply calculations to the fields that are being analyzed. Tableau tool contains various categories of built-in tableau functions that can be applied directly to your uploaded data.
To apply the Tableau function:
Step 1: Create a calculated field by clicking on the Analysis tab in the tableau worksheet, and then click on the option Create calculated field option from the drop-down menu.
Step 2: After clicking on this option, you will find a drop-down menu that contains a list of all tableau functions with their description and example. You can select any of the tableau functions from the drop-down menu or search in the text box.
Step 3: Click on OK and then drag and drop the calculated field from measure/Dimension in the Sheet to see the results.
The various types of Tableau functions are:
- Numeric functions
- String functions
- Date functions
- Type Conversion functions
- Aggregate functions
- Logical functions
Numeric Functions
These functions are used to perform calculations on the data values in your fields. They only take numerical values as inputs.
The most commonly used numeric Tableau functions are:
- ABS: It gives the absolute value of a number.
Syntax: ABS (number)
Example: ABS ([10.75]). = 11
- ACOS: It will return the arc cosine of the given number in Radians.
Syntax: ACOS (number)
Example: ACOS (-1) = 3.14159265358979
- ASIN: It will return the arc sine of the given number in Radians.
Syntax: ASIN (number)
Example: ASIN (1) = 1.5707963267949
- ATAN: It will return the arctangent of the given number in Radians.
Syntax: ATAN (number)
Example: ATAN (180) = 1.5652408283942
- CEILING: It will return the given number rounded off to the nearest integer of equal or greater value.
Syntax: CEILING (number)
Example: CEILING (3.1415) = 4
- COS: It will return the cosine of the given angle specified in Radians.
Syntax: COS (number)
Example: COS (PI ()/4) = 0.707106781186548
- COT: It will return the cotangent of the given angle specified in Radians.
Syntax: COT (number)
Example: CO1 (PI ()/4) = 1
- DEGREES: It will return the value of the given angle in Degrees.
Syntax: DEGREES (number)
Example: DEGREES (PI ()/4) = 45
- DIV: It will return the integer value of the quotient, given the Dividend and Divisor.
Syntax: DIV (integer1, integer2)
Example: DIV (11, 2) = 5
- EXP: It will return the value of e raised to the power of the given number.
Syntax: EXP (number)
Example: EXP (2) = 7.389
- FLOOR: It will return the given number rounded off to the nearest integer of equal or lesser value.
Syntax: FLOOR (number)
Example: FLOOR (6.1415) = 6
- LN: It will return the natural log of the given number.
Syntax: LN (number)
Example: LN (1) = 0
- LOG: It will return the log with base 10 of the given number.
Syntax: LOG (number, [base])
Example: LOG (1) = 0
- MAX: It will return the maximum of the passed arguments.
Syntax: MAX (number, number)
Example: MAX (4, 7) = 7
- MIN: It returns the minimum of the passed arguments.
Syntax: MIN (number, number)
Example: MIN (4, 7) = 4
- PI: It will return the value of Pi.
Syntax: PI () = 3.142
- POWER: It will return the value of the first argument raised to the power of the second argument.
Syntax: POWER (number, power)
Example: POWER (2, 10) = 1024
- RADIANS: It will return the value of the given angle in Radians.
Syntax: RADIANS (number)
Example: RADIANS (45) = 0.785397
- ROUND: It will return the given number rounded off to the specified number of decimal places.
Syntax: ROUND (number, [decimal place])
Example: ROUND ([Profit])
- SIGN: It will return the sign of a given number.
Syntax: SIGN (number)
Example: SIGN (AVG (Profit)) = -1
- SIN: It will return the sine of the given angle specified in Radians.
Syntax: SIN (number)
Example: SIN (PI ()/4) = 0.707106781186548
- SQRT: It gives the square root of the given number.
Syntax: SQRT (number)
Example: SQRT (25) = 5
- SQUARE: It will return the square of the given number.
Syntax: SQUARE (number)
Example: SQUARE (5) = 25
- TAN: It will return the tangent of the given angle specified in Radians.
Syntax: TAN (number)
Example: TAN (PI ()/4) = 1
String Functions
These functions are used to handle string data.
The most commonly used String Tableau functions are:
- ASCII: It will return the ASCII code for the first character of the said string.
Syntax: ASCII (string)
Example: ASCII (‘A’) = 65
- CHAR: It will return the character represented by the ASCII code.
Syntax: CHAR (ASCII code)
Example: CHAR (65) = ‘A’
- CONTAINS: This function will return true if the string contains the substring.
Syntax: CONTAINS (string, substring)
Example: CONTAINS (“Tableau”, “able”) = true
- ENDSWITH: It will return true if the given string ends with said substring.
Syntax: ENDSWITH (string, substring)
Example: ENDSWITH (“Tableau”, “able”) = true
- FIND: It returns the index position of the substring in the string if the string contains said substring, else 0. The function ignores any such substring that appears before the index position start if the optional argument start is added.
Syntax: FIND (string, substring, [start])
Example: FIND (“Tableau”, “able”) = 4
- FINDNTH: This function returns the index position of the nth occurrence of the substring in the string if the string contains said substring.
Syntax: FINDNTH (string, substring, occurrence)
Example: FIND (“Tableau”, “a”, 2) = 6
- LEFT: It will return the left-most number of characters in the given string.
Syntax: LEFT (string, number)
Example: LEFT (“Tableau”, 3) = “Tab”
- LEN: This returns the length of the string.
Syntax: LEN (string)
Example: LEN (“Tableau”) = 7
- LOWER: It will return the entire given string in lowercase alphabets.
Syntax: LOWER (string)
Example: LOWER (“Tableau”) = tableau
- LTRIM: It will return the given string without any preceding space.
Syntax: LTRIM (string)
Example: LTRIM (“ Tableau ”) = “Tableau “
- MAX: It will return the maximum of the two passed string arguments.
Syntax: MAX (a, b)
Example: MAX (“Apple”, “Banana”) = “Banana”
- MID: It will return the given string from the index position of start.
Syntax: MID (string, start, [length])
Example: MID (“Tableau”, 3) = “leau”
- MIN: It returns the minimum of the two passed string arguments.
Syntax: MIN (a, b)
Example: MIN (“Apple”, “Banana”) = “Apple”
- REPLACE: It will search the given string for the substring and replaces it with the replacement.
Syntax: REPLACE (string, substring, replacement)
Example: REPLACE (“Version8.0”, “8.0”, “9.0”) = “Version9.0”
- RIGHT: It will return the rightmost number of characters in the given string.
Syntax: RIGHT (string, number)
Example: RIGHT (“Tableau”, 3) = “eau”
- RTRIM: It will return the given string without any succeeding space.
Syntax: RTRIM (string)
Example: RTRIM (“ Tableau ”) = ” Tableau”
- SPACE: It will return a string consisting of a specified number of spaces.
Syntax: SPACE (number)
Example: SPACE (1) = ” “
- SPLIT: It will return a substring from a string, using a delimiter character to divide the string into a sequence of tokens.
Syntax: SPLIT (string, delimiter, token number)
Example: SPLIT (‘a-b-c-d’, ‘-‘, 2) = ‘b’
- STARTSWITH: It returns true if the given string starts with said substring.
Syntax: STARTSWITH (string, substring)
Example: STARTSWITH (“Tableau”, “Tab”) = true
- TRIM: It will return the given string without any preceding or succeeding space.
Syntax: TRIM (string)
Example: TRIM (“ Tableau ”) = “Tableau”
- UPPER: It will return the entire given string in uppercase alphabets.
Syntax: UPPER (string)
Example: UPPER (“Tableau”) = TABLEAU
Date Functions
These functions are used to handle Dates in your data source such as year, month, date, day, and/or time.
The most commonly used Date functions in Tableau are:
- DATEADD: It will return the specified date with the specified number interval added to the specified date_part of said date.
Syntax: DATEADD (date_part, interval, date)
Example: DATEADD (‘month’, 3, #2019-09-17#) = 2019-12-17 12:00:00 AM
- DATEDIFF: It will return the difference between both the dates expressed in units of the date part.
Syntax: DATEDIFF (date_part, date1, date2, [startof_week])
Example: DATEDATEDIFF (‘week’, #2019-12-15#, #2019-12-17#, ‘monday’) = 1
- DATENAME: It will return the date part of the date in string form.
Syntax: DATENAME (date_part, date, [startof_week])
Example: DATENAME (‘month’, #2019-12-17#) = December
- DATEPART: It will return the date part of the date in integer form.
Syntax: DATEPART (date_part, date, [startof_week])
Example: DATEPART (‘month’, #2019-12-17#) = 12
- DATETRUNC: It will return the truncated form of the specified date to the accuracy specified by the date part.
Syntax: DATETRUNC (date_part, date, [startof_week])
Example: DATETRUNC (‘quarter’, #2019-12-17#) = 2019-07-01 12:00:00 AM
- DAY: It will return the day of the given date in integer form.
Syntax: DAY (Date)
Example: DAY (#2019-12-17#) = 17
- ISDATE: This function returns true if the given a string is a valid date.
Syntax: ISDATE (String)
Example: ISDATE (December 17, 2019) = true
- MAKEDATE: It will return the date value constructed from the specified year, month, and date.
Syntax: MAKEDATE (year, month, day)
Example: MAKEDATE (2019, 12, 17) = #December 17, 2019#
- MAKEDATETIME: It will return the date and time values constructed from the specified year, month, and date and the hour, minute, and second.
Syntax: MAKEDATETIME (date, time)
Example: MAKEDATETIME (“2019-12-17”, #11:28:28PM#) = #12/17/2019 11:28:28 PM#
- MAKETIME: It will return the time value constructed from the specified hour, minute, and second.
Syntax: MAKETIME (hour, minute, second)
Example: MAKETIME (11, 28, 28) = #11:28:28#
- MONTH: It will return the month of the given date in integer form.
Syntax: MONTH (Date)
Example: MONTH (#2019-12-17#) = 12
- NOW: This will retrieve the current date and time.
Syntax: NOW ()
Example: NOW () = 2019-12-17 11:28:28 PM
- TODAY: It will return the current date.
Syntax: TODAY ()
Example: TODAY () = 2019-12-17
- YEAR: It will return the year of the given date in integer form.
Syntax: YEAR (Date)
Example: YEAR (#2019-12-17#) = 2019
Type Conversion Functions
These Tableau functions convert fields from one data type to another data type.
The most commonly used Type Conversion Functions in Tableau are:
- DATE: This function returns a date if given a number, string, or date expression.
Syntax: DATE (expression)
Example: DATE ([Employee Start Date])
DATE (“December 17, 2019”) = #December 17, 2019#
DATE (#2019-12-17 14:52#) = #2019-12-17#
- DATETIME: This function returns a date-time if the given is a number, string, or date expression.
Syntax: DATETIME (expression)
Example: DATETIME (“December 17, 2019 07:59:00”) = December 17, 2019 07:59:00
- DATEPARSE: This function returns a date-time in the specified format of the given string.
Syntax: DATEPARSE (format, string)
Example: DATEPARSE (“dd.MMMM.yyyy”, “17.December.2019”) = #December 17, 2019#
- FLOAT: It will cast its argument as a floating-point number.
Syntax: FLOAT (expression)
Example: FLOAT (3) = 3.000
- INT: It will cast its argument as an integer.
Syntax: INT (expression)
Example: INT (8.0/3.0) = 2
- STRING: It will cast its argument as a string.
Syntax: STR (expression)
Example: STR ([Date])
Aggregate Functions
These functions are used to summarize or change the granularity of your data.
The most commonly used Aggregate functions in Tableau are:
- ATTR: It will return the value of the expression if it contains a single value for all the rows (ignoring the NULL values) else returns an asterisk.
Syntax: ATTR (expression)
- AVG: It will return the mean of all the values in an expression (ignoring the NULL values). AVG is only used with numeric fields.
Syntax: AVG (expression)
- COLLECT: This is an aggregate calculation that combines the values in the argument field, ignoring the null values.
Syntax: COLLECT (Spatial)
- CORR: It will return the Pearson correlation coefficient between the two expressions. The Pearson correlation measure the linear relationship between the two variables results ranging from -1 to +1 inclusive in which 1 denotes an exact positive linear relationship, 0 denotes no linear relationship between the variance, and −1 denotes an exact negative relationship.
Syntax: CORR (expr1, expr2)
- COUNT: It will return the count of items in a group (ignoring the NULL values). This means that if there are multiple numbers of the same item, this function will count them as separate items and not a single item.
Syntax: COUNT (expression)
- COUNTD: It will return the distinct count of items in a group (ignoring the NULL values). This means that if there are multiple numbers of the same item, this function will count it as a single item.
Syntax: COUNTD (expression)
- COVAR: It will return the Sample Covariance of two expressions. A positive covariance signifies that the variables tend to move in the same direction, which means when the value of one variable grows, the other will also grow.
Syntax: COVAR (expr1, EXPR2)
- COVARP: It will return the Population Covariance of two expressions. Population covariance is used when there is data available for all items of interest for the entire population, not just a sample.
Syntax: COVARP (expr1, EXPR2)
- MAX: It will return the maximum of an expression across all records, ignoring NULL values.
Syntax: MAX (expression)
- MEDIAN: It will return the median of an expression across all records, ignoring NULL values.
Syntax: MEDIAN (expression)
- MIN: It will return the minimum of an expression across all records, ignoring NULL values.
Syntax: MIN (expression)
- PERCENTILE: It will return the percentile value of a given expression. This number returned should be between 0 and 1 – for example, 0.34, and must be a numeric constant.
Syntax: PERCENTILE (expression, number)
- STDEV: It will return the statistical Standard Deviation of all values in the given expression based on a sample of the population.
Syntax: STDEV (expression)
- STDEVP: It will return the statistical Standard Deviation of all values in the given expression based on the biased population.
Syntax: STDEVP (expression)
- SUM: This returns the sum of all values in the expression (ignoring the NULL values). SUM is only used with numeric fields.
Syntax: SUM (expression)
- VAR: This function returns the statistical variance of all given expression based on a sample of the population.
Syntax: VAR (expression)
- VARP: This function returns the statistical variance of all given values expression based on the entire population.
Syntax: VARP (expression)
Logical Functions
These functions are used to determine if a certain condition is true or false (Boolean logic).
The most commonly used Logical functions in Tableau are:
- AND: This function performs logical AND (conjunction) between two expressions. When both conditions specified have fulfilled, it will return true.
Syntax: IF <expression1> AND <expression2> THEN <then> END
Example: IF (ATTR ([Market]) = “Asia” AND SUM ([Sales]) > [Emerging Threshold]) THEN “Well Performing”
- CASE: This function performs logical tests and returns appropriate values, comparable to SWITCH CASE in most common programming languages. When a value that matches the condition specified in the given expression, it returns the corresponding value. If no such match is found, the default expression is returned. If there is no default expression and no values match, it returns NULL. It is often easy to use than IF or IF THEN ELSE.
Syntax: CASE <expression1> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> … ELSE <default return> END
Example: CASE [Region] WHEN ‘West’ THEN 1 WHEN ‘East’ THEN 2 ELSE 3 END
- ELSE & IF, THEN: This function tests a series of inputs returning the THEN value for the first expression that fulfills your IF condition.
Syntax: IF <expr1> THEN <then> ELSE <else> END
Example: IF [Profit] > 0 THEN ‘Profit’ ELSE ‘Loss’ END
- ELSEIF: This function tests a series of inputs returning the THEN value for the first expression that fulfills your ESLEIF condition.
Syntax: IF <expr1> THEN <then> [ELSEIF <expr2> THEN <then2>…] ELSE <else> END
Example: IF [Profit] > 0 THEN ‘Profit’ ELSEIF [Profit] = 0 THEN ‘No Profit No Loss’ ELSE ‘Loss’ END
- END: This function ends with an expression.
Syntax: IF <expr1> THEN <then> [ELSEIF <expr2> THEN <then2>…] ELSE <else> END
Example: IF [Profit] > 0 THEN ‘Profit’ ELSEIF [Profit] = 0 THEN ‘No Profit No Loss’ ELSE ‘Loss’ END
- IFNULL: This function returns expr1, not NULL, else returns expr2.
Syntax: IFNULL (expr1, expr2)
Example: IFNULL ([Profit], 0)
- IIF: This function checks whether a condition is fulfilled, it returns a value if the condition is TRUE, another if FALSE, and a third value or NULL if unknown.
Syntax: IIF (test, then, else, [unknown])
Example: IIF ([Profit] > 0, ‘Profit’, ‘Loss’, 0)
- ISDATE: This function returns true if a given string is a valid date.
Syntax: ISDATE (String)
Example: ISDATE (“2004-04-15”) = True
- ISNULL: This function returns true if a given expression contains valid data.
Syntax: ISNULL (expression)
Example: ISNULL ([Profit])
- NOT: This function performs logical NOT on given expression.
Syntax: IF NOT <expr1> THEN <then> END
Example: IF NOT [Profit] > 0 THEN “No Profit” END
- OR: This function performs logical OR on two expressions. Returns true if the condition is valid.
Syntax: IF <expr1> OR <expr2> THEN <then> END
Example: IF [Profit] < 0 OR [Profit] = 0 THEN “Needs Improvement” END
- WHEN: This function finds the first specified value that fulfills the condition and returns the corresponding return.
Syntax: CASE <expr> WHEN <Value1> THEN <return1> … [ELSE <else>] END
Example: CASE [RomanNumberals] WHEN ‘I’ THEN 1 WHEN ‘II’ THEN 2 ELSE 3 END
- ZN: It will return the given expression if it is not NULL, else returns zero.
Syntax: ZN (expression)
Example: ZN ([Profit])
