Tableau Prep Functions Reference

Tableau Prep calculation functions allow you to use calculated fields to create new data using data that already exists in your data source.

Number functions

Syntax Description
ABS(number) Returns the absolute value of the given number.

Examples:

ABS(-7) = 7

ABS([Budget Variance])

Returns the absolute value for all the numbers contained in the Budget Variance field.
ACOS(number) Returns the arc cosine of the given number. The result is in radians.

Example:

ACOS(-1) = 3.14159265358979

ASIN(number) Returns the arc sine of a given number. The result is in radians.

Example:

ASIN(1) = 1.5707963267949

ATAN(number) Returns the arc tangent of a given number. The result is in radians.

Example:

ATAN(180) = 1.5652408283942

ATAN2(y number, x number) Returns the arc tangent of two given numbers (x and y). The result is in radians.

Example:

ATAN2(2, 1) = 1.10714871779409

CEILING(number) Rounds a number to the nearest integer of equal or greater value.

Example:

CEILING(3.1415) = 4

COS(number) Returns the cosine of an angle. Specify the angle in radians.

Example:

COS(PI( ) /4) = 0.707106781186548

COT(angle) Returns the cotangent of an angle. Specify the angle in radians.

Example:

COT(PI( ) /4) = 1

DIV(integer1, integer2) Returns the integer part of a division operation, in which integer1 is divided by integer2.

Example:

DIV(11,2) = 5

EXP(number) Returns e raised to the power of the given number.

Examples:

EXP(2) = 7.389 EXP(-[Growth Rate]*[Time])

FLOOR(number) Rounds a number to the nearest integer of equal or lesser value.

Example:

FLOOR(3.1415) = 3

HEXBINX(number, number) 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.

Example:

HEXBINX([Longitude], [Latitude])

HEXBINY(number, number) Maps an x, y coordinate to the y-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.

Example:

HEXBINY([Longitude], [Latitude])

LN(number)

Returns the natural logarithm of a number. Returns Null if number is less than or equal to 0.

Example:

LN(EXP(5)) = 5

LOG(number [, base])

Returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used.

Example:

LOG(256, 2) = 8

MAX(number, number) Returns the maximum of the two arguments, which must be of the same type. Returns Null if either argument is Null. MAX can also be applied to a single field in an aggregate calculation.

Examples:

MAX(4,7)
MAX(Sales,Profit)
MAX([First Name],[Last Name])

MIN(number, number) Returns the minimum of the two arguments, which must be of the same type. Returns Null if either argument is Null. MIN can also be applied to a single field in an aggregate calculation.

Examples:

MIN(4,7)

MIN(Sales,Profit)

MIN([First Name],[Last Name])

PI( )

Returns the numeric constant pi: 3.14159.

Example:

PI() = 3.14159265358979

POWER(number, power) Raises the number to the specified power.

Examples:

POWER(5,2) = 52 = 25 POWER(Temperature, 2)

You can also use the ^ symbol:

5^2 = POWER(5,2) = 25

RADIANS(number) Converts the given number from degrees to radians.

Example:

RADIANS(180) = 3.14159

ROUND(number, [decimals]) Rounds numbers to a specified number of digits. The decimals argument specifies how many decimal points of precision to include in the final result. If decimals is omitted, number is rounded to the nearest integer.

Example:

This example rounds every Sales value to an integer:

ROUND(Sales)

SIGN(number) Returns the sign of a number: The possible return values are -1 if the number is negative, 0 if the number is zero, or 1 if the number is positive.

Example:

If the average of the profit field is negative, then

SIGN(AVG(Profit)) = -1

SIN(number) Returns the sine of an angle. Specify the angle in radians.

Examples:

SIN(0) = 1.0

SIN(PI( )/4) = 0.707106781186548

SQRT(number) Returns the square root of a number.

Example:

SQRT(25) = 5

SQUARE(number) Returns the square of a number.

Example:

SQUARE(5) = 25

TAN(number)

Returns the tangent of an angle. Specify the angle in radians.

Example:

TAN(PI ( )/4) = 1.0

ZN(expression)

Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.

Example:

ZN([Profit]) = [Profit]

String functions

Syntax Description
ASCII(string) Returns the ASCII code for the first character of string.

Example:

ASCII('A') = 65

CHAR(number) Returns the character encoded by the ASCII code number.

Example:

CHAR(65) = 'A'

CONTAINS(string, substring) Returns true if the given string contains the specified substring.

Example:

CONTAINS(“Calculation”, “alcu”) = true

ENDSWITH(string, substring) Returns true if the given string ends with the specified substring. Trailing white spaces are ignored.

Example:

ENDSWITH(“Tableau”, “leau”) = true

FIND(string, substring, [start]) Returns the index position of substring in string, or 0 if the substring isn't found. If the optional argument start is added, the function ignores any instances of substring that appear before the index position start. The first character in the string is position 1.

Examples:

FIND("Calculation", "alcu") = 2

FINDNTH(string, substring, occurrence) Returns the position of the nth occurrence of substring within the specified string, where n is defined by the occurrence argument.

Example:

FINDNTH("Calculation", "a", 2) =7

ISDATE(string)

Returns true if a given string is a valid date.

Example:

ISDATE("2004-04-15") = True

LEFT(string, number) Returns the left-most number of characters in the string.

Example:

LEFT("Matador", 4) = "Mata"

LEN(string) Returns the length of the string.

Example:

LEN("Matador") = 7

LOWER(string) Returns string, with all characters lowercase.

Example:

LOWER("ProductVersion") = "productversion"

LTRIM(string) Returns the string with any leading spaces removed.

Example:

Example: LTRIM(" Sales") = "Sales"

(MID(string, start, [length]) Returns the string starting at index position start. The first character in the string is position 1. If the optional argument length is added, the returned string includes only that number of characters.

Examples:

MID("Calculation", 2) = "alculation"

MID("Calculation", 2, 5) ="alcul"

PROPER(string) Converts a text string so the first letter of each word is capitalized and the remaining letters are in lowercase. Spaces and non-alphanumeric characters such as punctuation also act as separators.

Example:

PROPER("PRODUCT name") = "Product Name"

REGEXP_EXTRACT(string, pattern) Returns a substring of the given string that matches the capturing group within the regular expression pattern. The regular expression pattern requires exactly one capturing group

Example:

REGEXP_EXTRACT('abc 123', '[a-z]+\\s+(\\d+)') = '123'

REGEXP_EXTRACT_NTH(string, pattern, index) Returns a substring of the given string using the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index.

Example:

REGEXP_EXTRACT_NTH('abc 123', '([a-z]+)\\s+(\\d+)', 2) = '123'

REGEXP_MATCH(string, pattern) Returns true if a substring of the provided string matches the regular expression pattern.

Example:

REGEXP_MATCH('-([1234].[ The.Market ])-', '\\[\\s*(\\w*\\.)(\\w*\\s*\\])') = true

REGEXP_REPLACE(string, pattern, replacement) Returns a copy of the given string where the matching pattern is substituted with the replacement string.

Example:

REGEXP_REPLACE('abc 123', '\\s', '-') = 'abc--123'

REPLACE(string, substring, replacement) Searches string for substring and replaces it with replacement. If substring is not found, the string is not changed.

Example:

REPLACE("Calculation", "ion", "ed") = "Calculated"

RIGHT(string, number) Returns the right-most number of characters in string.

Example:

RIGHT("Calculation", 4) = "tion"

RTRIM(string) Returns string with any trailing spaces removed.

Example:

Example: RTRIM("Market ") = "Market"

SPACE(number) Returns a string that is composed of the specified number of repeated spaces.

Example:

SPACE(1) = " "

SPLIT(string, delimiter, token number)

Returns a substring from a string, as determined by a delimiter extracting the characters from the beginning or end of the string.

Example:

SPLIT('a-b-c-d', '-', 2) = 'b'

STARTSWITH(string, substring) Returns true if string starts with substring. Leading white spaces are ignored.

Example:

STARTSWITH(“Joker”, “Jo”) = true

TRIM(string) Returns the string with leading and trailing spaces removed.

Example:

TRIM(" Calculation ") = "Calculation"

UPPER(string) Returns string, with all characters uppercase.

Example:

UPPER("Calculation") = "CALCULATION"

Aggregate functions

Syntax Description
AVG(expression) Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored

Example;

AVG([Profit])

COUNT(expression) Returns the number of items in a group. Null values are not counted.

Example:

COUNT([Customer ID])

COUNTD(expression) Returns the number of distinct items in a group. NULL values are not counted. Each unique value is counted only once.

Example:

COUNTD([Region])

MEDIAN(expression) Returns the median of a single expression. MEDIAN can be used with numeric fields only. Null values are ignored.

Example:

MEDIAN([Profit])

PERCENTILE(expression, number) Aggregate calculation that returns the percentile value from the given expression corresponding to the specified number. Valid values for the number are 0 through 1. PERCENTILE([expression], 0.50) will always return the median value in the expression.

Example:

PERCENTILE([Sales], 0.90)

STDEV(expression) Returns the sample standard deviation of the expression.

Example:

STDEV([Profit])

STDEVP(expression)

Returns the population standard deviation of the expression.

Example:

STDEVP([Profit])

SUM(expression)

Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored.

Example:

SUM([Profit])

VAR(expression)

Returns the statistical variance of all values in the given expression based on a sample of the population.

Example:

VAR([Profit])

VARP(expression)

Returns the statistical variance of all values in the given expression based on a sample of the population.

Example:

VARP([Profit])

ZN(expression)

Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.

Example:

ZN([Profit]) = [Profit]

Type conversion functions

Syntax Description
DATE Returns a date given a number, string, or date expression.

Example:

DATE("2006-06-15 14:52") = 2006-06-15

Note that the quotation marks are required.

DATETIME(expression) Returns a datetime given a number, string, or date expression.

Example:

DATETIME("April 15, 2004 07:59:00")

Note that the quotation marks are required.

FLOAT(expression) Returns a float given an expression of any type. This function requires unformatted numbers which means exclude commas and other symbols.

Example:

FLOAT("3")= 3.000

INT(expression) Returns an integer given an expression. This function truncates results to the closest integer toward zero.

Example:

INT(8.0/3.0) = 2 or INT(-9.7) = -9

MAKEDATE(year, month, day) Returns a date value constructed from a year, a month and a day of the month.

Example:

MAKEDATE(2014, 3, 18)

MAKEDATETIME(date, time)

Returns a date and time value given a date expression and a time expression.

Example:

MAKEDATETIME(#2012-11-12#, #07:59:00#)

MAKETIME(hour, minute, second)

Returns a time value constructed from hours, minutes and seconds.

Example:

MAKETIME(14, 52, 40)

STR(expression)

Returns a string given an expression.

Example:

STR([Age])

Returns all of the values of the Age measure as strings.

Date functions

Syntax Description
DATEADD(date_part, interval, date) Adds an increment to the specified date and returns the new date. The increment is defined by the interval and the date_part.

Example:

DATEADD('month', 3, #2004-04-15#) = 2004-07-15 12:00:00 AM

DATEDIFF(date_part, start_date, end_date, [start_of_week])

Returns the difference between two dates where start_date is subtracted from end_date. The difference is expressed in units of date_part. If start_of_week is omitted, the week start day is determined by the start day configured for the data source.

Example:

DATEDIFF('month', #2004-07-15#, #2004-04-03#, 'sunday') = -3

DATENAME(date_part, date, [start_of_week]) Returns a part of the given date as a string, where the part is defined by date_part. If start_of_week is omitted, the week start day is determined by the start day configured for the data source.

Example:

DATENAME('month', #2004-04-15#) = "April"

DATEPARSE(format, string) Converts a string to a date in the specified format.

Example:

DATEPARSE ("dd.MMMM.yyyy", "15.April.2004") = 2004-04-15 12:00:00 AM

DATEPARTT(date_part, date, [start_of_week]) Returns a part of the given date as an integer where the part is defined by date_part. If start_of_week is omitted, the week start day is determined by the start day configured for the data source.

Example:

DATEPART('month', #2004-04-15#) = 4

DATETRUNC(date_part, date, [start_of_week]) Truncates the specified date to the accuracy specified by the date_part and returns the new date. If start_of_week is omitted, the week start day is determined by the start day configured for the data source.

Example:

DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM

DAY(date) Returns the day of the given date as an integer.

Example:

DAY(#2004-04-12#) = 12

ISDATE(string) Returns true if a given string is a valid date.

Example:

ISDATE("2004-04-15") = True

MONTH(date)

Returns the month of a given date as an integer.

Example:

MONTH(#2004-04-12#) = 4

NOW() Returns the current date and time.

Example:

NOW() = 2004-05-12 1:08:21 PM

TODAY() Returns the current date.

Example:

TODAY() = 2004-05-12

YEAR(date) Returns the year of a given date as an integer.

Example:

YEAR(#2004-04-12#) = 2004

Logical functions

Syntax Description
IFNULL(expr1, expr2) Returns <expr1> if it is not null, otherwise returns <expr2>.

Example:

IFNULL([Profit], 0)

IIF(test, then, else, [unknown]) Checks whether a condition is met, and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown.

Example:

IIF([Profit] > 0, 'Profit', 'Loss')

ISDATE(string) Returns true if a given string is a valid date.

Example:

ISDATE("2004-04-15") = True

ISNULL(expression) Returns true if the expression does not contain valid data (Null).

Example:

ISNULL([Profit])

ZN(expression)

Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.

Example:

ZN([Profit]) = [Profit]

Analytic functions

Syntax Description
LAST_VALUE(expression, [return_last_non-null_value]) Returns the last value of the given expression, from the first row in the partition to the current row. Accepts an optional second boolean parameter to return the last non-null value.

Example:

{ ORDERBY [Row ID] ASC: LAST_VALUE([Category], true) } = returns the last non-null value of [Category]

LOOKUP(expression, [offset])

Returns the value of the given expression in a target row, specified as a relative offset from the current row. Returns NULL if the target row cannot be determined.

The [offset] parameter of the LOOKUP function can only take a literal integer such as 1.

Example:

{ORDERBY [DATE] ASC : LOOKUP([SALES], -1)}

Returns the SALES value from the previous row.

NTILE(number) Distributes rows in a selected partition into a specified number of groups or tiles. With this function the set of values (6,9,9,14) distributed to 3 tiles would be assigned to the tiles (1,2,2,3) in ascending order.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: NTILE(3) }}

RANK() Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: RANK() }}

RANK_DENSE() Returns the dense rank for the current row in the partition. Identical values are assigned an identical rank, but no gaps are inserted into the number sequence.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: RANK_DENSE() }}

RANK_MODIFIED() Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: RANK_MODIFIED() }}

RANK_PERCENTILE() Returns the percentile rank for the current row in the partition.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: RANK_PERCENTILE() }}

ROW_NUMBER() Returns a unique sequential row ID to each row.

Example:

{PARTITION [Customer]: {ORDERBY [Sales] ASC: ROW_NUMBER() }}

RUNNING_AVG(expression) Returns the running average of the given expression, from the first row in the partition to the current row.

Example:

{ PARTITION [Category] : { ORDERBY [Row ID] ASC : RUNNING_AVG([Sales]) } } = running average of Sales, per Category.

RUNNING_SUM(expression) Returns the running sum of the given expression, from the first row in the partition to the current row.

Example:

{ ORDERBY [Row ID] ASC : RUNNING_SUM([Sales]) } = running sum of all Sales

Keywords

Function Description
AND Performs a logical conjunction on two expressions

Example:

IF (ATTR([Market]) = "New Business" AND SUM([Sales]) > [Emerging Threshold] )THEN "Well Performing"

ASC Defines the ascending order for ORDERBY operations.

Example

{ORDERBY [Sales] ASC:RANK()}

CASE Finds the first <value> that matches <expr> and returns the corresponding <return>.

Example:

CASE [RomanNumeral] WHEN "|" THEN 1 WHEN "II" THEN 2 ELSE 3 END

DESC Defines the descending order for ORDERBY operations.

Example:

{ORDERBY [Sales] DESC:RANK()}

ELSE Test a series of expressions returning the <then> value for the first true<expr>.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSE 'Loss' END

ELSEIF Test a series of expressions returning the <then> value for the first true<expr>. Equivalent to putting an if statement inside an ELSE operation.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

END Terminates a operation introduced by the corresponding directive.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

{ FIXED } FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail.

Example:

{FIXED[Customer ID]:MIN([Order Date])}

IF Create conditional statement (IF statement), and allows you to execute only if a condition is True.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

NOT Performs a logical negation on an expression.

Example:

IF NOT [Profit] > 0 THEN "Unprofitable" END

OR Performs a logical disjunction on an expression.

Example:

IF [Profit] < 0 OR [Profit] = 0 THEN "Needs Improvement" END

{ ORDERBY } Defines the order to apply analytic functions. Use the optional asc | desc argument to specify ascending or descending order for each field.

Example:

{ORDERBY [Customer Name] ASC, [Sales] DESC: RANK() }

{ PARTITION } Defines the group to analytic functions. ORDERBY must be inside the partition.

Example:

{PARTITION [Customer]: {ORDERBY [Sales]: RANK()}}

THEN Test a series of expressions returning the <then> value for the first true<expr>.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Break even' ELSE 'unprofitable' END

WHEN Finds the first <value> that matches <expr> and returns the corresponding <return>.

Example:

CASE [RomanNumberal] WHEN 'I' THEN 1 WHEN 'II' THEN 2 ELSE 3 END

Thanks for your feedback!Your feedback has been successfully submitted. Thank you!