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:
|
ACOS(number)
|
Returns the arc cosine of the given number. The result is in radians.
Example:
|
ASIN(number)
|
Returns the arc sine of a given number. The result is in radians. Example:
|
ATAN(number)
|
Returns the arc tangent of a given number. The result is in radians.
Example:
|
ATAN2(y number, x number)
|
Returns the arc tangent of two given numbers (x and y). The result is in radians.
Example:
|
CEILING(number) |
Rounds a number to the nearest integer of equal or greater value. Example:
|
COS(number)
|
Returns the cosine of an angle. Specify the angle in radians. Example:
|
COT(angle)
|
Returns the cotangent of an angle. Specify the angle in radians.
Example:
|
DIV(integer1, integer2)
|
Returns the integer part of a division operation, in which integer1 is divided by integer2.
Example:
|
EXP(number)
|
Returns e raised to the power of the given number. Examples:
|
FLOOR(number)
|
Rounds a number to the nearest integer of equal or lesser value.
Example:
|
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:
|
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:
|
LN(number)
|
Returns the natural logarithm of a number. Returns Null if number is less than or equal to 0. Example:
|
LOG(number [, base]) |
Returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used. Example:
|
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:
|
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:
|
PI( )
|
Returns the numeric constant pi: 3.14159. Example:
|
POWER(number, power)
|
Raises the number to the specified power.
Examples:
You can also use the ^ symbol:
|
RADIANS(number)
|
Converts the given number from degrees to radians. Example:
|
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:
|
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
|
SIN(number)
|
Returns the sine of an angle. Specify the angle in radians.
Examples:
|
SQRT(number)
|
Returns the square root of a number.
Example:
|
SQUARE(number)
|
Returns the square of a number. Example:
|
TAN(number) |
Returns the tangent of an angle. Specify the angle in radians. Example:
|
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:
|
String functions
| Syntax | Description |
|---|---|
ASCII(string)
|
Returns the ASCII code for the first character of string. Example:
|
CHAR(number)
|
Returns the character encoded by the ASCII code number. Example:
|
CONTAINS(string, substring)
|
Returns true if the given string contains the specified substring. Example:
|
ENDSWITH(string, substring)
|
Returns true if the given string ends with the specified substring. Trailing white spaces are ignored.Example:
|
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:
|
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:
|
ISDATE(string)
|
Returns true if a given string is a valid date. Example:
|
LEFT(string, number)
|
Returns the left-most number of characters in the string.
Example:
|
LEN(string)
|
Returns the length of the string. Example:
|
LOWER(string)
|
Returns string, with all characters lowercase. Example:
|
LTRIM(string)
|
Returns the string with any leading spaces removed. Example:
|
(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:
|
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:
|
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_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_MATCH(string, pattern)
|
Returns true if a substring of the provided string matches the regular expression pattern. Example:
|
REGEXP_REPLACE(string, pattern, replacement)
|
Returns a copy of the given string where the matching pattern is substituted with the replacement string.
Example:
|
REPLACE(string, substring, replacement)
|
Searches string for substring and replaces it with replacement. If substring is not found, the string is not changed. Example:
|
RIGHT(string, number)
|
Returns the right-most number of characters in string. Example:
|
RTRIM(string)
|
Returns string with any trailing spaces removed. Example:
|
SPACE(number)
|
Returns a string that is composed of the specified number of repeated spaces. Example:
|
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:
|
STARTSWITH(string, substring)
|
Returns true if string starts with substring. Leading white spaces are ignored.Example:
|
TRIM(string)
|
Returns the string with leading and trailing spaces removed. Example:
|
UPPER(string)
|
Returns string, with all characters uppercase. Example:
|
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;
|
COUNT(expression)
|
Returns the number of items in a group. Null values are not counted. Example:
|
COUNTD(expression)
|
Returns the number of distinct items in a group. NULL values are not counted. Each unique value is counted only once. Example:
|
MEDIAN(expression)
|
Returns the median of a single expression. MEDIAN can be used with numeric fields only. Null values are ignored. Example:
|
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:
|
STDEV(expression)
|
Returns the sample standard deviation of the expression. Example:
|
STDEVP(expression)
|
Returns the population standard deviation of the expression. Example:
|
SUM(expression)
|
Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored. Example:
|
VAR(expression)
|
Returns the statistical variance of all values in the given expression based on a sample of the population. Example:
|
VARP(expression)
|
Returns the statistical variance of all values in the given expression on the entire population. Example:
|
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:
|
Type conversion functions
| Syntax | Description |
|---|---|
DATE
|
Returns a date given a number, string, or date expression. Example:
Note that the quotation marks are required. |
DATETIME(expression)
|
Returns a datetime given a number, string, or date expression. Example:
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:
|
INT(expression)
|
Returns an integer given an expression. This function truncates results to the closest integer toward zero. Example:
|
MAKEDATE(year, month, day)
|
Returns a date value constructed from a year, a month and a day of the month. Example:
|
MAKEDATETIME(date, time)
|
Returns a date and time value given a date expression and a time expression. Example:
|
MAKETIME(hour, minute, second)
|
Returns a time value constructed from hours, minutes and seconds. Example:
|
STR(expression)
|
Returns a string given an expression. Example:
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:
|
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:
|
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:
|
DATEPARSE(format, string)
|
Converts a string to a date in the specified format. Example:
|
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:
|
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:
|
DAY(date)
|
Returns the day of the given date as an integer. Example:
|
ISDATE(string)
|
Returns true if a given string is a valid date.
Example:
|
MONTH(date)
|
Returns the month of a given date as an integer. Example:
|
NOW()
|
Returns the current date and time. Example:
|
TODAY()
|
Returns the current date. Example:
|
YEAR(date)
|
Returns the year of a given date as an integer. Example:
|
Logical functions
| Syntax | Description |
|---|---|
IFNULL(expr1, expr2)
|
Returns <expr1> if it is not null, otherwise returns <expr2>.Example:
|
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:
|
ISDATE(string)
|
Returns true if a given string is a valid date. Example:
|
ISNULL(expression)
|
Returns true if the expression does not contain valid data (Null).Example:
|
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:
|
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:
|
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 [ Example:
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:
|
RANK()
|
Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Example:
|
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:
|
RANK_MODIFIED()
|
Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank. Example:
|
RANK_PERCENTILE()
|
Returns the percentile rank for the current row in the partition. Example:
|
ROW_NUMBER()
|
Returns a unique sequential row ID to each row.
Example:
|
RUNNING_AVG(expression)
|
Returns the running average of the given expression, from the first row in the partition to the current row.
Example:
|
RUNNING_SUM(expression)
|
Returns the running sum of the given expression, from the first row in the partition to the current row. Example:
|
Keywords
| Function | Description |
|---|---|
AND
|
Performs
a logical conjunction on two expressions Example:
|
ASC
|
Defines the ascending order for ORDERBY operations.Example
|
CASE
|
Finds the first <value> that matches <expr> and returns the corresponding <return>.Example:
|
DESC
|
Defines the descending order for ORDERBY operations. Example:
|
ELSE
|
Test a series of expressions returning the <then> value for the first true<expr>. Example:
|
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:
|
END
|
Terminates a operation introduced by the corresponding directive. Example:
|
{ FIXED }
|
FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail. Example:
|
IF
|
Create conditional statement (IF statement), and allows you to execute only if a condition is True.Example:
|
NOT
|
Performs a logical negation on an expression. Example:
|
OR
|
Performs a logical disjunction on an expression. Example:
|
{ ORDERBY } |
Defines the order to apply analytic functions. Use the optional asc | desc argument to specify ascending or descending order for each field. Example:
|
{ PARTITION }
|
Defines the group to analytic functions. ORDERBY must be inside the partition. Example:
|
THEN
|
Test a series of expressions returning the <then> value for the first true<expr>. Example:
|
WHEN
|
Finds the first <value> that matches <expr> and returns the corresponding <return>. Example:
|