Tableau Functions (Alphabetical)
The Tableau functions in this reference are organised alphabetically. Click a letter to jump to that location in the list. You can also use Ctrl+F (Command-F on a Mac) to open a search box to look for a specific function.
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
A
ABS
Syntax | ABS(number) |
Output | Number (positive) |
Definition | Returns the absolute value of the given <number> . |
Example | ABS(-7) = 7 The second example returns the absolute value for all the numbers contained in the Budget Variance field. |
Notes | See also SIGN . |
ACOS
Syntax | ACOS(number) |
Output | Number (angle in radians) |
Definition | Returns the arccosine (angle) of the given <number> . |
Example | ACOS(-1) = 3.14159265358979 |
Notes | The inverse function, COS , takes the angle in radians as the argument and returns the cosine. |
AND
Syntax | <expr1> AND <expr2> |
Definition | Performs a logical conjunction on two expressions. (If both sides are true, the logical test returns true.) |
Output | Boolean |
Example | IF [Season] = "Spring" AND "[Season] = "Fall" "If both (Season = Spring) and (Season = Fall) are true simultaneously, then return It's the apocalypse and footwear doesn't matter." |
Notes | Often used with IF and IIF. See also NOT and OR. If both expressions are If you create a calculation in which the result of an Note: The |
AREA
Syntax | AREA(Spatial Polygon, 'units') |
Output | Number |
Definition | Returns the total surface area of a <spatial polygon> . |
Example | AREA([Geometry], 'feet') |
Notes | Supported unit names (must be in quotation marks in the calculation, such as
|
ASCII
Syntax | ASCII(string) |
Output | Number |
Definition | Returns the ASCII code for the first character of a <string> . |
Example | ASCII('A') = 65 |
Notes | This is the inverse of the CHAR function. |
ASIN
Syntax | ASIN(number) |
Output | Number (angle in radians) |
Definition | Returns the arcsine (angle) of a given <number> . |
Example | ASIN(1) = 1.5707963267949 |
Notes | The inverse function, SIN , takes the angle in radians as the argument and returns the sine. |
ATAN
Syntax | ATAN(number) |
Output | Number (angle in radians) |
Definition | Returns the arctangent (angle) of a given <number> . |
Example | ATAN(180) = 1.5652408283942 |
Notes | The inverse function, |
ATAN2
Syntax | ATAN2(y number, x number) |
Output | Number (angle in radians) |
Definition | Returns the arctangent (angle) between two numbers (<y number> and <x number> ). The result is in radians. |
Example | ATAN2(2, 1) = 1.10714871779409 |
Notes | See also ATAN , TAN and COT . |
ATTR
Syntax | ATTR(expression) |
Definition | Returns the value of the <expression> if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored. |
AVG
Syntax | AVG(expression) |
Definition | Returns the average of all the values in the <expression> . Null values are ignored. |
Notes | AVG can only be used with numeric fields. |
B
BUFFER
Syntax | BUFFER(Spatial Point, distance, 'units') |
Output | Geometry |
Definition | Returns a polygon shape centred over a <spatial point> , with a radius determined by the <distance> and <unit> values. |
Example | BUFFER([Spatial Point Geometry], 25, 'mi') BUFFER(MAKEPOINT(47.59, -122.32), 3, 'km') |
Notes | Supported unit names (must be in quotation marks in the calculation, such as
|
C
CASE
Syntax | CASE <expression>
|
Output | Depends on data type of the <then> values. |
Definition | Evaluates the |
Example |
"Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return Sneakers." |
Notes | Used with WHEN, THEN, ELSE and END. Tip: Many times you can use a group to get the same results as a complicated CASE function, or use CASE to replace native grouping functionality, such as in the previous example. You may want to test which is more performant for your scenario. |
CEILING
Syntax | CEILING(number) |
Output | Integer |
Definition | Rounds a <number> to the nearest integer of equal or greater value. |
Example | CEILING(2.1) = 3 |
Notes | See also FLOOR and ROUND . |
Database limitations |
|
CHAR
Syntax | CHAR(number) |
Output | String |
Definition | Returns the character encoded by the ASCII code <number> . |
Example | CHAR(65) = 'A' |
Notes | This is the inverse of the ASCII function. |
COLLECT
Syntax | COLLECT(spatial) |
Definition | An aggregate calculation that combines the values in the argument field. Null values are ignored. |
Notes | COLLECT can only be used with spatial fields. |
CONTAINS
Syntax | CONTAINS(string, substring) |
Output | Boolean |
Definition | Returns true if the given <string> contains the specified <substring> . |
Example | CONTAINS("Calculation", "alcu") = true |
Notes | See also the logical function(Link opens in a new window) IN as well as supported RegEx in the additional functions documentation(Link opens in a new window). |
CORR
Syntax | CORR(expression1, expression2) |
Output | Number from -1 to 1 |
Definition | Returns the Pearson correlation coefficient of two expressions. |
Example | example |
Notes | 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, 0 denotes no linear relationship between the variance and −1 is an exact negative relationship. The square of a CORR result is equivalent to the R-Squared value for a linear trend line model. See Trend Line Model Terms(Link opens in a new window). Use with table-scoped LOD expressions: You can use CORR to visualise correlation in a disaggregated scatter using a table-scoped level of detail expression(Link opens in a new window). For example: {CORR(Sales, Profit)} With a level of detail expression, the correlation is run over all rows. If you used a formula like |
Database limitations |
For other data sources, consider either extracting the data or using |
COS
Syntax | COS(number) The number argument is the angle in radians. |
Output | Number |
Definition | Returns the cosine of an angle. |
Example | COS(PI( ) /4) = 0.707106781186548 |
Notes | The inverse function, See also |
COT
Syntax | COT(number) The number argument is the angle in radians. |
Output | Number |
Definition | Returns the cotangent of an angle. |
Example | COT(PI( ) /4) = 1 |
Notes | See also ATAN , TAN and PI . |
COUNT
Syntax | COUNT(expression) |
Definition | Returns the number of items. Null values are not counted. |
COUNTD
Syntax | COUNTD(expression) |
Definition | Returns the number of distinct items in a group. Null values are not counted. |
COVAR
Syntax | COVAR(expression1, expression2) |
Definition | Returns the sample covariance of two expressions. |
Notes | Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Sample covariance uses the number of non-null data points n-1 to normalise the covariance calculation, rather than n, which is used by the population covariance (available with the If The value of |
Database limitations |
For other data sources, consider either extracting the data or using |
COVARP
Syntax | COVARP(expression 1, expression2) |
Definition | Returns the population covariance of two expressions. |
Notes | Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Population covariance is sample covariance multiplied by (n-1)/n, where n is the total number of non-null data points. Population covariance is the appropriate choice when there is data available for all items of interest as opposed to when there is only a random subset of items, in which case sample covariance (with the If |
Database limitations |
For other data sources, consider either extracting the data or using |
D
DATE
Type conversion function that changes string and number expressions into dates, as long as they are in a recognisable format.
Syntax | DATE(expression) |
Output | Date |
Definition | Returns a date given a number, string or date <expression> . |
Example | DATE([Employee Start Date]) DATE("September 22, 2018") DATE("9/22/2018") DATE(#2018-09-22 14:52#) |
Notes | Unlike
|
DATEADD
Adds a specified number of date parts (months, days, etc) to the starting date.
Syntax | DATEADD(date_part, interval, date) |
Output | Date |
Definition | Returns the specified date with the specified number <interval> added to the specified ><date_part of that date. For example, adding three months or 12 days to a starting date. |
Example | Push out all due dates by one week DATEADD('week', 1, [due date]) Add 280 days to the date February 20, 2021 DATEADD('day', 280, #2/20/21#) = #November 27, 2021# |
Notes | Supports ISO 8601 dates. |
DATEDIFF
Returns the number of date parts (weeks, years, etc) between two dates.
Syntax | DATEDIFF(date_part, date1, date2, [start_of_week]) |
Output | Integer |
Definition | Returns the difference between date1 and date2 expressed in units of date_part . For example, subtracting the dates someone entered and left a band to see how long they were in the band. |
Example | Number of days between 25th March 1986 and 20th February 2021 DATEDIFF('day', #3/25/1986#, #2/20/2021#) = 12,751 How many months someone was in a band DATEDIFF('month', [date joined band], [date left band]) |
Notes | Supports ISO 8601 dates. |
DATENAME
Returns the name of the specified date part as a discrete string.
Syntax | DATENAME(date_part, date, [start_of_week]) |
Output | String |
Definition | Returns <date_part> of date as a string. |
Example | DATENAME('year', #3/25/1986#) = "1986" DATENAME('month', #1986-03-25#) = "March" |
Notes | Supports ISO 8601 dates. A very similar calculation is DATEPART, which returns the value of the specified date part as a continuous integer. By changing the attributes of the calculation’s result (dimension or measure, continuous or discrete) and the date formatting, the results of An inverse function is |
DATEPARSE
Returns specifically formatted strings as dates.
Syntax | DATEPARSE(date_format, date_string) |
Output | Date |
Definition | The <date_format> argument describes how the <date_string> field is arranged. Because of the variety of ways the string field can be ordered, the <date_format> must match exactly. For a full explanation, see Convert a Field to a Date Field(Link opens in a new window). |
Example | DATEPARSE('yyyy-MM-dd', "1986-03-25") = #March 25, 1986# |
Notes |
Inverse functions, which take dates apart and return the value of their parts, are |
Database limitations |
|
DATEPART
Returns the name of the specified date part as an integer.
Syntax | DATEPART(date_part, date, [start_of_week]) |
Output | Integer |
Definition | Returns <date_part> of date as an integer. |
Example | DATEPART('year', #1986-03-25#) = 1986 DATEPART('month', #1986-03-25#) = 3 |
Notes | Supports ISO 8601 dates. A very similar calculation is An inverse function is |
DATETIME
Syntax | DATETIME(expression) |
Output | Datetime |
Definition | Returns a datetime given a number, string or date expression. |
Example | DATETIME("April 15, 2005 07:59:00") = April 15, 2005 07:59:00 |
DATETRUNC
This function can be thought of as date rounding. It takes a specific date and returns a version of that date at the desired specificity. Because every date must have a value for day, month, quarter and year, DATETRUNC
sets the values as the lowest value for each date part up to the date part specified. Refer to the example for more information.
Syntax | DATETRUNC(date_part, date, [start_of_week]) |
Output | Date |
Definition | Truncates the <date> to the accuracy specified by the <date_part> . This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. |
Example | DATETRUNC('day', #9/22/2018#) = #9/22/2018# DATETRUNC('iso-week', #9/22/2018#) = #9/17/2018# (the Monday of the week containing 22/09/2018) DATETRUNC(quarter, #9/22/2018#) = #7/1/2018# (the first day of the quarter containing 22/09/2018) Note: For week and iso-week, the |
Notes | Supports ISO 8601 dates. You shouldn't use For example, |
DAY
Returns the day of the month (1–31) as an integer.
Syntax | DAY(date) |
Output | Integer |
Definition | Returns the day of the given <date> as an integer. |
Example | Day(#September 22, 2018#) = 22 |
Notes | See also WEEK , MONTH , Quarter , YEAR and the ISO equivalents |
DEGREES
Syntax | DEGREES(number) The number argument is the angle in radians. |
Output | Number (degrees) |
Definition | Converts an angle in radians to degrees. |
Example | DEGREES(PI( )/4) = 45.0 |
Notes | The inverse function, See also |
DISTANCE
Syntax | DISTANCE(<SpatialPoint1>, <SpatialPoint2>, 'units') |
Output | Number |
Definition | Returns the distance measurement between two points in the specified units . |
Example | DISTANCE([Origin Point],[Destination Point], 'km') |
Notes | Supported unit names (must be in quotation marks in the calculation):
|
Database limitations | This function can only be created with a live connection but will continue to work if the data source is converted to an extract. |
DIV
Syntax | DIV(integer1, integer2) |
Output | Integer |
Definition | Returns the integer part of a division operation, in which <integer1> is divided by <integer2> . |
Example | DIV(11,2) = 5 |
DOMAIN
DOMAIN(string_url)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
E
ELSE
Syntax | CASE <expression>
|
Definition | An optional piece of an IF or CASE expression used to specify a default value to return if none of the tested expressions are true. |
Example | IF [Season] = "Summer" THEN 'Sandals' CASE [Season] |
Notes | Used with CASE, WHEN, IF, ELSEIF, THEN and END
|
ELSEIF
Syntax | [ELSEIF <test2> THEN <then2>] |
Definition | An optional piece of an IF expression used to specify additional conditions beyond the initial IF. |
Example | IF [Season] = "Summer" THEN 'Sandals' |
Notes | Used with IF, THEN, ELSE and END
Unlike |
END
Definition | Used to close an IF or CASE expression. |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return Sneakers." |
Notes |
ENDSWITH
ENDSWITH
Syntax | ENDSWITH(string, substring) |
Output | Boolean |
Definition | Returns true if the given <string> ends with the specified <substring> . Trailing white spaces are ignored. |
Example | ENDSWITH("Tableau", "leau") = true |
Notes | See also the supported RegEx in the additional functions documentation(Link opens in a new window). |
EXCLUDE
For more information, see Level of Detail Expressions(Link opens in a new window).
EXP
Syntax | EXP(number) |
Output | Number |
Definition | Returns e raised to the power of the given <number> . |
Example | EXP(2) = 7.389 |
Notes | See also LN . |
F
FIND
Syntax | FIND(string, substring, [start]) |
Output | Number |
Definition | Returns the index position of If the optional numeric argument |
Example | FIND("Calculation", "alcu") = 2 FIND("Calculation", "Computer") = 0 FIND("Calculation", "a", 3) = 7 FIND("Calculation", "a", 2) = 2 FIND("Calculation", "a", 8) = 0 |
Notes | See also the supported RegEx in the additional functions documentation(Link opens in a new window). |
FINDNTH
Syntax | FINDNTH(string, substring, occurrence) |
Output | Number |
Definition | Returns the position of the nth occurrence of <substring> within the specified <string> , where n is defined by the <occurence> argument. |
Example | FINDNTH("Calculation", "a", 2) = 7 |
Notes |
See also the supported RegEx in the additional functions documentation(Link opens in a new window). |
FIRST
FIRST()
For more information, see Table Calculation Functions(Link opens in a new window).
FIXED
For more information, see Level of Detail Expressions(Link opens in a new window).
FLOAT
Syntax | FLOAT(expression) |
Output | Floating point number (decimal) |
Definition | Casts its argument as a floating point number. |
Example | FLOAT(3) = 3.000 |
Notes | See also INT , which returns an integer. |
FLOOR
Syntax | FLOOR(number) |
Output | Integer |
Definition | Rounds a <number> to the nearest integer of equal or lesser value. |
Example | FLOOR(7.9) = 7 |
Notes | See also CEILING and ROUND . |
Database limitations |
|
FULLNAME
Syntax | FULLNAME( ) |
Output | String |
Definition | Returns the full name for the current user. |
Example | FULLNAME( ) This returns the full name of the signed-in user, such as "Hamlin Myrer". [Manager] = FULLNAME( ) If manager "Hamlin Myrer" is signed in, this example returns TRUE only if the Manager field in the view contains "Hamlin Myrer". |
Notes | This function checks:
User filters When used as a filter, a calculated field such as |
G
GET_JSON_OBJECT
GET_JSON_OBJECT(JSON string, JSON path)
Only supported when connected to Hadoop Hive. For more information, see Additional Functions(Link opens in a new window).
GROUP_CONCAT
GROUP_CONCAT(expression)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
H
HEXBINX
Syntax | HEXBINX(number, number) |
Output | Number |
Definition | 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]*2.5, [Latitude]*2.5) |
Notes | HEXBINX and HEXBINY are binning and plotting functions for hexagonal bins. Hexagonal bins are an efficient and elegant option for visualising data in an x/y plane such as a map. Because the bins are hexagonal, each bin closely approximates a circle and minimises variation in the distance from the data point to the centre of the bin. This makes the clustering both more accurate and informative. |
HEXBINY
Syntax | HEXBINY(number, number) |
Output | Number |
Definition | 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]*2.5, [Latitude]*2.5) |
Notes | See also HEXBINX . |
HOST
HOST(string_url)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
I
IF
Syntax | IF <test1> THEN <then1> |
Output | Depends on data type of the <then> values. |
Definition | Tests a series of expressions and returns the |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." |
Notes |
IFNULL
Syntax | IFNULL(expr1, expr2) |
Output | Depends on the data type of the <expr> values. |
Definition | Returns |
Example | IFNULL([Assigned Room], "TBD") "If the Assigned Room field isn't null, return its value. If the Assigned room field is null, return TBD instead." |
Notes | Compare with ISNULL. See also ZN. |
IIF
Syntax | IIF(<test>, <then>, <else>, [<unknown>]) |
Output | Depends on the data type of the values in the expression. |
Definition | Checks whether a condition is met (<test> ) and returns <then> if the test is true, <else> if the test is false and an optional value for <unknown> if the test is null. If the optional unknown isn't specified, IIF returns null. |
Example | IIF([Season] = 'Summer', 'Sandals', 'Other footwear') "If Season = Summer, then return Sandals. If not, return Other footwear" IIF([Season] = 'Summer', 'Sandals', "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither are true, return Sneakers." IIF('Season' = 'Summer', 'Sandals', "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If none of the expressions are true, return Sneakers." |
Notes |
That is to say, in the calculation below, the result will be Red, not Orange, because the expression stops being evaluated as soon as A=A is evaluated as true:
|
IN
Syntax | <expr1> IN <expr2> |
Output | Boolean (true or false) |
Definition | Returns TRUE if any value in <expr1> matches any value in <expr2> . |
Example | SUM([Cost]) IN (1000, 15, 200) "Is the value of the Cost field 1000, 15 or 200?" [Field] IN [Set] "Is the value of the field present in the set?" |
Notes | The values in See also WHEN. |
INCLUDE
For more information, see Level of Detail Expressions(Link opens in a new window).
INDEX
INDEX( )
For more information, see Table Calculation Functions(Link opens in a new window).
INT
Syntax | INT(expression) |
Output | Integer |
Definition | Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero. |
Example | INT(8/3) = 2 INT(-9.7) = -9 |
Notes | When a string is converted to an integer it is first converted to a float and then rounded. See also |
INTERSECTS
Syntax | INTERSECTS (<geometry1>, <geometry2>) |
Output | Boolean |
Definition | Returns true or false indicating whether two geometries overlap in space. |
Notes | Supported combinations: point/polygon, line/polygon and polygon/polygon. |
ISDATE
Checks if the string is a valid date format.
Syntax | ISDATE(string) |
Output | Boolean |
Definition | Returns true if a given <string> is a valid date. |
Example | ISDATE(09/22/2018) = true ISDATE(22SEP18) = false |
Notes | The required argument must be a string. ISDATE cannot be used for a field with a date data type – the calculation will return an error. |
ISFULLNAME
Syntax | ISFULLNAME("User Full Name") |
Output | Boolean |
Definition | Returns |
Example | ISFULLNAME("Hamlin Myrer") |
Notes | The This function checks:
|
ISMEMBEROF
Syntax | ISMEMBEROF("Group Name") |
Output | Boolean or null |
Definition | Returns |
Example | ISMEMBEROF('Superstars') ISMEMBEROF('domain.lan\Sales') |
Notes | The If the user is signed in to Tableau Cloud or Tableau Server, group membership is determined by Tableau groups. The function will return TRUE if the given string is "All Users" The |
ISNULL
Syntax | ISNULL(expression) |
Output | Boolean (true or false) |
Definition | Returns true if the |
Example | ISNULL([Assigned Room]) "Is the Assigned Room field null?" |
Notes | Compare with IFNULL. See also ZN. |
ISOQUARTER
Syntax | ISOQUARTER(date) |
Output | Integer |
Definition | Returns the ISO8601 week-based quarter of a given <date> as an integer. |
Example | ISOQUARTER(#1986-03-25#) = 1 |
Notes | See also ISOWEEK , ISOWEEKDAY , ISOYEAR and the non-ISO equivalents. |
ISOWEEK
Syntax | ISOWEEK(date) |
Output | Integer |
Definition | Returns the ISO8601 week-based week of a given <date> as an integer. |
Example | ISOWEEK(#1986-03-25#) = 13 |
Notes | See also ISOWEEKDAY , ISOQUARTER , ISOYEAR and the non-ISO equivalents. |
ISOWEEKDAY
Syntax | ISOWEEKDAY(date) |
Output | Integer |
Definition | Returns the ISO8601 week-based weekday of a given <date> as an integer. |
Example | ISOWEEKDAY(#1986-03-25#) = 2 |
Notes | See also ISOWEEK , ISOQUARTER , ISOYEAR and the non-ISO equivalents |
ISOYEAR
Syntax | ISOYEAR(date) |
Output | Integer |
Definition | Returns the ISO8601 week-based year of a given date as an integer. |
Example | ISOYEAR(#1986-03-25#) = 1,986 |
Notes | See also ISOWEEK , ISOWEEKDAY , ISOQUARTER and the non-ISO equivalents. |
ISUSERNAME
Syntax | ISUSERNAME("username") |
Output | Boolean |
Definition | Returns TRUE if the current user's username matches the specified <username> or FALSE if it does not match. |
Example | ISUSERNAME("hmyrer") |
Notes | The This function checks:
|
J
K
L
LAST
LAST()
For more information, see Table Calculation Functions(Link opens in a new window).
LEFT
Syntax | LEFT(string, number) |
Output | String |
Definition | Returns the left-most <number> of characters in the string. |
Example | LEFT("Matador", 4) = "Mata" |
Notes | See also MID and RIGHT. |
LEN
Syntax | LEN(string) |
Output | Number |
Definition | Returns the length of the <string> . |
Example | LEN("Matador") = 7 |
Notes | Not to be confused with the spatial function LENGTH . |
LENGTH
Syntax | LENGTH(geometry, 'units') |
Output | Number |
Definition | Returns the geodetic path length of the line string or strings in the <geometry> using the given <units> . |
Example | LENGTH([Spatial], 'metres') |
Notes | The result is Not to be confused with the string function |
LN
Syntax | LN(number) |
Output | Number The output is |
Definition | Returns the natural logarithm of a <number> . |
Example | LN(50) = 3.912023005 |
Notes | See also EXP and LOG . |
LOG
Syntax | LOG(number, [base]) If the optional base argument isn't present, base 10 is used. |
Output | Number |
Definition | Returns the logarithm of a <number> for the given <base> . |
Example | LOG(16,4) = 2 |
Notes | See also POWER LN . |
LOG2
LOG2(number)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
LOOKUP
LOOKUP(expression, [offest])
For more information, see Table Calculation Functions(Link opens in a new window).
LOWER
Syntax | LOWER(string) |
Output | String |
Definition | Returns the provided <string> in all lowercase characters. |
Example | LOWER("ProductVersion") = "productversion" |
Notes | See also UPPER and PROPER. |
LTRIM
Syntax | LTRIM(string) |
Output | String |
Definition | Returns the provided <string> with any leading spaces removed. |
Example | LTRIM(" Matador ") = "Matador " |
Notes | See also RTRIM. |
LTRIM_THIS
LTRIM_THIS(string, string)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
M
MAKEDATE
Syntax | MAKEDATE(year, month, day) |
Output | Date |
Definition | Returns a date value constructed from the specified <year> , <month> and <day> . |
Example | MAKEDATE(1986,3,25) = #1986-03-25# |
Notes | Note: Incorrectly entered values will be adjusted into a date, such as Available for Tableau Data Extracts. Check for availability in other data sources.
|
MAKEDATETIME
Syntax | MAKEDATETIME(date, time) |
Output | Datetime |
Definition | Returns a datetime that combines a <date> and a <time> . The date can be a date, datetime or a string type. The time must be a datetime. |
Example | MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM# MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM# |
Notes | This function is available only for MySQL-compatible connections (which for Tableau are MySQL and Amazon Aurora).
|
MAKELINE
Syntax | MAKELINE(SpatialPoint1, SpatialPoint2) |
Output | Geometry (line) |
Definition | Generates a line mark between two points |
Example | MAKELINE(MAKEPOINT(47.59, -122.32), MAKEPOINT(48.5, -123.1)) |
Notes | Useful for building origin-destination maps. |
MAKEPOINT
Syntax | MAKEPOINT(latitude, longitude, [SRID]) |
Output | Geometry (point) |
Definition | Converts data from If the optional |
Example | MAKEPOINT(48.5, -123.1) MAKEPOINT([AirportLatitude], [AirportLongitude]) MAKEPOINT([Xcoord],[Ycoord], 3493) |
Notes |
SRID is a spatial reference identifier that uses ESPG reference system codes(Link opens in a new window) to specify coordinate systems. If SRID is not specified, WGS84 is assumed and parameters are treated as latitude/longitude in degrees. You can use |
MAKETIME
Syntax | MAKETIME(hour, minute, second) |
Output | Datetime |
Definition | Returns a date value constructed from the specified <hour> , <minute> and <second> . |
Example | MAKETIME(14, 52, 40) = #1/1/1899 14:52:40# |
Notes | Because Tableau does not support a time data type, only datetime, the output is a datetime. The date portion of the field will be 1/1/1899. Similar function to |
MAX
Syntax | MAX(expression) or MAX(expr1, expr2) |
Output | Same data type as the argument, or NULL if any part of the argument is null. |
Definition | Returns the maximum of the two arguments, which must be of the same data type.
|
Example | MAX(4,7) = 7 |
Notes | For strings
For database data sources, the For dates For dates, the As an aggregation
As a comparison
See also |
MEDIAN
Syntax | MEDIAN(expression) |
Definition | Returns the median of an expression across all records. Null values are ignored. |
Notes | MEDIAN can only be used with numeric fields. |
Database limitations |
For other data source types, you can extract your data into an extract file to use this function. See Extract Your Data(Link opens in a new window). |
MID
Syntax | (MID(string, start, [length]) |
Output | String |
Definition | Returns a string starting at the specified If the optional numeric argument |
Example | MID("Calculation", 2) = "alculation" MID("Calculation", 2, 5) ="alcul" |
Notes | See also the supported RegEx in the additional functions documentation(Link opens in a new window). |
MIN
Syntax | MIN(expression) or MIN(expr1, expr2) |
Output | Same data type as the argument, or NULL if any part of the argument is null. |
Definition | Returns the minimum of the two arguments, which must be of the same data type.
|
Example | MIN(4,7) = 4 |
Notes | For strings
For database data sources, the For dates For dates, the As an aggregation
As a comparison
See also |
Model Extensions
For more information, see Table Calculation Functions(Link opens in a new window).
- MODEL_EXTENSION_BOOL
- MODEL_EXTENSION_INT
- MODEL_EXTENSION_REAL
- MODEL_EXTENSION_STR
MODEL_PERCENTILE
Syntax | MODEL_PERCENTILE(
|
Definition | Returns the probability (between 0 and 1) of the expected value being less than or equal to the observed mark, defined by the target expression and other predictors. This is the Posterior Predictive Distribution Function, also known as the Cumulative Distribution Function (CDF). |
Example | MODEL_PERCENTILE( SUM([Sales]),COUNT([Orders])) |
MODEL_QUANTILE
Syntax | MODEL_QUANTILE(
|
Definition | Returns a target numeric value within the probable range defined by the target expression and other predictors, at a specified quantile. This is the Posterior Predictive Quantile. |
Example | MODEL_QUANTILE(0.5, SUM([Sales]), COUNT([Orders])) |
MONTH
Syntax | MONTH(date) |
Output | Integer |
Definition | Returns the month of the given <date> as an integer. |
Example | MONTH(#1986-03-25#) = 3 |
Notes | See also DAY , WEEK , Quarter , YEAR and the ISO equivalents |
N
NOT
Syntax | NOT <expression> |
Output | Boolean (true or false) |
Definition | Performs logical negation on an expression. |
Example | IF NOT [Season] = "Summer" "If Season doesn't equal Summer, then return Don't wear sandals. If not, return Wear sandals." |
Notes | Often used with IF and IIF. See also The Tableau functions in this reference are organised alphabetically. Click a letter to jump to that location in the list. You can also use Ctrl+F (Command-F on a Mac) to open a search box to look for a specific function. and OR. |
NOW
Syntax | NOW() |
Output | Datetime |
Definition | Returns the current local system date and time. |
Example | NOW() = 1986-03-25 1:08:21 PM |
Notes |
See also If the data source is a live connection, the system date and time could be in another time zone. For more information on how to address this, see the Knowledge Base. |
O
OR
Syntax | <expr1> OR <expr2> |
Output | Boolean (true or false) |
Definition | Performs a logical disjunction on two expressions. |
Example | IF [Season] = "Spring" OR [Season] = "Fall" "If either (Season = Spring) or (Season = Fall) is true, then return Sneakers." |
Notes | Often used with IF and IIF. See also The Tableau functions in this reference are organised alphabetically. Click a letter to jump to that location in the list. You can also use Ctrl+F (Command-F on a Mac) to open a search box to look for a specific function. and NOT. If either expression is If you create a calculation which displays the result of an Note: The |
OUTLINE
Syntax | OUTLINE(<spatial polygon>) |
Output | Geometry |
Definition | Converts a polygon geometry into linestrings. |
Notes | Useful for creating a separate layer for an outline that can be styled differently than the fill. Supports polygons within multipolygons. |
P
PARSE_URL
PARSE_URL(string, url_part)
Only supported when connected to Cloudera Impala. For more information, see Additional Functions(Link opens in a new window).
PARSE_URL_QUERY
PARSE_URL_QUERY(string, key)
Only supported when connected to Cloudera Impala. For more information, see Additional Functions(Link opens in a new window).
PERCENTILE
Syntax | PERCENTILE(expression, number) |
Definition | Returns the percentile value from the given <expression> corresponding to the specified <number> . The <number> must be between 0 and 1 (inclusive) and must be a numeric constant. |
Example | PERCENTILE([Score], 0.9) |
Database limitations | This function is available for the following data sources: Non-legacy Microsoft Excel and Text File connections, Extracts and extract-only data source types (for example, Google Analytics, OData or Salesforce), Sybase IQ 15.1 and later data sources, Oracle 10 and later data sources, Cloudera Hive and Hortonworks Hadoop Hive data sources, EXASolution 4.2 and later data sources. For other data source types, you can extract your data into an extract file to use this function. See Extract Your Data(Link opens in a new window). |
PI
Syntax | PI() |
Output | Number |
Definition | Returns the numeric constant pi: 3.14159... |
Example | PI() = 3.14159 |
Notes | Useful for trig functions that take their input in radians. |
POWER
Syntax | POWER(number, power) |
Output | Number |
Definition | Raises the <number> to the specified <power> . |
Example | POWER(5,3) = 125 |
Notes | You can also use the ^ symbol, such as 5^3 = POWER(5,3) = 125 |
PREVIOUS_VALUE
PREVIOUS_VALUE(expression)
For more information, see Table Calculation Functions(Link opens in a new window).
PROPER
Syntax | PROPER(string) |
Output | String |
Definition | Returns the provided |
Example | PROPER("PRODUCT name") = "Product Name" PROPER("darcy-mae") = "Darcy-Mae" |
Notes | Spaces and non-alphanumeric characters such as punctuation are treated as separators. |
Q
Quarter
Syntax | QUARTER(date) |
Output | Integer |
Definition | Returns the quarter of the given <date> as an integer. |
Example | QUARTER(#1986-03-25#) = 1 |
Notes | See also DAY , WEEK , MONTH , YEAR and the ISO equivalents |
R
RADIANS
Syntax | RADIANS(number) |
Output | Number (angle in radians) |
Definition | Converts the given <number> from degrees to radians. |
Example | RADIANS(180) = 3.14159 |
Notes | The inverse function, DEGREES , takes an angle in radians and returns the angle in degrees. |
RANK Table Calculation functions
For more information, see Table Calculation Functions(Link opens in a new window).
RANK(expression, ['asc' | 'desc'])
RANK_DENSE(expression, ['asc' | 'desc'])
RANK_MODIFIED(expression, ['asc' | 'desc'])
RANK_PERCENTILE(expression, ['asc' | 'desc'])
RANK_UNIQUE(expression, ['asc' | 'desc'])
RAWSQL functions
For more information, see Pass-Through Functions (RAWSQL)(Link opens in a new window).
RAWSQL_BOOL("sql_expr", [arg1], … [argN])
RAWSQL_DATE("sql_expr", [arg1], … [argN])
RAWSQL_DATETIME("sql_expr", [arg1], … [argN])
RAWSQL_INT("sql_expr", [arg1], … [argN])
RAWSQL_REAL("sql_expr", [arg1], … [argN])
RAWSQL_SPATIAL
RAWSQL_STR("sql_expr", [arg1], … [argN])
RAWSQLAGG_BOOL("sql_expr", [arg1], … [argN])
RAWSQLAGG_DATE("sql_expr", [arg1], … [argN])
RAWSQLAGG_DATETIME("sql_expr", [arg1], … [argN])
RAWSQLAGG_INT("sql_expr", [arg1], … [argN])
RAWSQLAGG_REAL("sql_expr", [arg1], … [argN])
RAWSQLAGG_STR("sql_expr", [arg1], … [argN])
REGEXP functions
For more information, see Additional Functions(Link opens in a new window).
REGEXP_EXTRACT(string, pattern)
REGEXP_EXTRACT_NTH(string, pattern, index)
REGEXP_EXTRACT_NTH(string, pattern, index)
REGEXP_MATCH(string, pattern)
REGEXP_REPLACE(string, pattern, replacement)
REPLACE
Syntax | REPLACE(string, substring, replacement |
Output | String |
Definition | Searches <string> for <substring> and replaces it with <replacement> . If <substring> is not found, the string is not changed. |
Example | REPLACE("Version 3.8", "3.8", "4x") = "Version 4x" |
Notes | See also REGEXP_REPLACE in the additional functions documentation(Link opens in a new window). |
RIGHT
Syntax | RIGHT(string, number) |
Output | String |
Definition | Returns the right-most <number> of characters in the string. |
Example | RIGHT("Calculation", 4) = "tion" |
Notes | See also LEFT and MID. |
ROUND
Syntax | ROUND(number, [decimals]) |
Output | Number |
Definition | Rounds the The optional |
Example | ROUND(1/3, 2) = 0.33 |
Notes | Some databases, such as SQL Server, allow specification of a negative length, where -1 rounds number to the tens place, -2 rounds to the hundreds place and so on. This is not true of all databases. For example, it is not true of Excel or Access. Tip: Because ROUND may run into issues due to the underlying floating point representation of numbers – such as 9.405 rounding to 9.40 – it may be preferable to format the number to the desired number of decimal points rather than rounding. Formatting 9.405 to two decimal places will yield the expected 9.41. |
RTRIM
Syntax | RTRIM(string) |
Output | String |
Definition | Returns the provided <string> with any trailing spaces removed. |
Example | RTRIM(" Calculation ") = " Calculation" |
Notes | See also LTRIM and TRIM. |
RTRIM_THIS
RTRIM_THIS(string, string)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
RUNNING Table Calculation functions
For more information, see Table Calculation Functions(Link opens in a new window).
RUNNING_AVG(expression)
RUNNING_COUNT(expression)
RUNNING_MAX(expression)
RUNNING_MIN(expression)
RUNNING_SUM(expression)
S
SCRIPT Analytics Extensions
For more information, see Table Calculation Functions(Link opens in a new window).
SCRIPT_BOOL
SCRIPT_INT
SCRIPT_REAL
SCRIPT_STR
SHAPETYPE
Syntax | SHAPETYPE(<geometry>) |
Output | String |
Definition | Returns a string describing the structure of the spatial geometry, such as Empty, Point, MultiPoint, LineString, MultiLinestring, Polygon, MultiPolygon, Mixed and unsupported |
Example | SHAPETYPE(MAKEPOINT(48.5, -123.1)) = "Point" |
SIGN
Syntax | SIGN(number) |
Output | -1, 0 or 1 |
Definition | 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 | SIGN(AVG(Profit)) = -1 |
Notes | See also ABS . |
SIN
Syntax | SIN(number) |
Output | Number |
Definition | Returns the sine of an angle in radians. |
Example | SIN(0) = 1.0 |
Notes | The inverse function, See also |
SIZE
SIZE()
For more information, see Table Calculation Functions(Link opens in a new window).
SPACE
Syntax | SPACE(number) |
Output | String (specifically, just spaces) |
Definition | Returns a string that is composed of the specified number of repeated spaces. |
Example | SPACE(2) = " " |
SPLIT
Syntax | SPLIT(string, delimiter, token number) |
Output | String |
Definition | Returns a substring from a <string> , using a <delimiter> character to divide the string into a sequence of <tokens> . |
Example | SPLIT ("a-b-c-d", "-", 2) = "b" SPLIT ("a|b|c|d", "|", -2) = "c" |
Notes | The string is interpreted as an alternating sequence of delimiters and tokens. So for the string
See also supported RegEX in the additional functions documentation(Link opens in a new window). |
Database limitations | The split and custom split commands are available for the following data sources types: Tableau data extracts, Microsoft Excel, Text File, PDF File, Salesforce, OData, Microsoft Azure Market Place, Google Analytics, Vertica, Oracle, MySQL, PostgreSQL, Teradata, Amazon Redshift, Aster Data, Google Big Query, Cloudera Hadoop Hive, Hortonworks Hive and Microsoft SQL Server. Some data sources impose limits on splitting strings. See SPLIT function limitations later in this topic. |
SQRT
Syntax | SQRT(number) |
Output | Number |
Definition | Returns the square root of a <number> . |
Example | SQRT(25) = 5 |
Notes | See also SQUARE . |
SQUARE
Syntax | SQUARE(number) |
Output | Number |
Definition | Returns the square of a <number> . |
Example | SQUARE(5) = 25 |
Notes | See also SQRT and POWER . |
STARTSWITH
Syntax | STARTSWITH(string, substring) |
Output | Boolean |
Definition | Returns true if string starts with substring . Leading white spaces are ignored. |
Example | STARTSWITH("Matador, "Ma") = TRUE |
Notes | See also CONTAINS, as well as supported RegEX in the additional functions documentation(Link opens in a new window). |
STDEV
Syntax | STDEV(expression) |
Definition | Returns the statistical standard deviation of all values in the given <expression> based on a sample of the population. |
STDEVP
Syntax | STDEVP(expression) |
Definition | Returns the statistical standard deviation of all values in the given <expression> based on a biased population. |
STR
Syntax | STR(expression) |
Output | String |
Definition | Casts its argument as a string. |
Example | STR([ID]) |
SUM
Syntax | SUM(expression) |
Definition | Returns the sum of all values in the <expression> . Null values are ignored. |
Notes | SUM can only be used with numeric fields. |
T
TAN
Syntax | TAN(number) The |
Output | Number |
Definition | Returns the tangent of an angle. |
Example | TAN(PI ( )/4) = 1.0 |
Notes | See also ATAN , ATAN2 , COT and PI . To convert an angle from degrees to radians, use RADIANS . |
THEN
Syntax | IF <test1> THEN <then1>
|
Definition | A required part of an IF , ELSEIF or CASE expression, used to define what result to return if a specific value or test is true. |
Example | IF [Season] = "Summer" THEN 'Sandals' "If Season = Summer, then return Sandals. If not, look at the next expression. If Season = Winter, then return Boots. If neither of the expressions are true, return Sneakers." CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return Sneakers." |
Notes |
TIMESTAMP_TO_USEC
TIMESTAMP_TO_USEC(expression)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
TLD
TLD(string_url)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
TODAY
Syntax | TODAY() |
Output | Date |
Definition | Returns the current local system date. |
Example | TODAY() = 1986-03-25 |
Notes |
See also If the data source is a live connection, the system date could be in another time zone. For more information on how to address this, see the Knowledge Base. |
TOTAL
TOTAL(expression)
For more information, see Table Calculation Functions(Link opens in a new window).
TRIM
Syntax | TRIM(string) |
Output | String |
Definition | Returns the provided <string> with leading and trailing spaces removed. |
Example | TRIM(" Calculation ") = "Calculation" |
Notes | See also LTRIM and RTRIM. |
U
UPPER
Syntax | UPPER(string) |
Output | String |
Definition | Returns the provided <string> in all uppercase characters. |
Example | UPPER("Calculation") = "CALCULATION" |
Notes | See also PROPER and LOWER. |
USEC_TO_TIMESTAMP
USEC_TO_TIMESTAMP(expression)
Only supported when connected to Google BigQuery. For more information, see Additional Functions(Link opens in a new window).
USERDOMAIN
Syntax | USERDOMAIN( ) |
Output | String |
Definition | Returns the domain for the current user. |
Notes | This function checks:
|
USERNAME
Syntax | USERNAME( ) |
Output | String |
Definition | Returns the username for the current user. |
Example | USERNAME( ) This returns the username of the signed-in user, such as "hmyrer". [Manager] = USERNAME( ) If manager "hmyrer" is signed in, this example returns TRUE only if the Manager field in the view contains "hmyrer". |
Notes | This function checks:
User filters When used as a filter, a calculated field such as |
USER ATTRIBUTE JSON web token functions
USERATTRIBUTE('attribute_name')
USERATTRIBUTEINCLUDES('attribute_name', 'expected_value')
For more information, see User Functions(Link opens in a new window).
V
VAR
Syntax | VAR(expression) |
Definition | Returns the statistical variance of all values in the given expression based on a sample of the population. |
VARP
Syntax | VARP(expression) |
Definition | Returns the statistical variance of all values in the given expression on the entire population. |
W
WEEK
Syntax | WEEK(date) |
Output | Integer |
Definition | Returns the week of the given <date> as an integer. |
Example | WEEK(#1986-03-25#) = 13 |
Notes | See also DAY , MONTH , Quarter , YEAR and the ISO equivalents |
WHEN
Syntax | CASE <expression> |
Definition | A required part of a CASE expression. Finds the first <value> that matches <expression> and returns the corresponding <then> . |
Example | CASE [Season] "Look at the Season field. If the value is Summer, then return Sandals. If the value is Winter, then return Boots. If none of the options in the calculation match what is in the Season field, return Sneakers." |
Notes | Used with CASE, THEN, ELSE and END.
CASE <expression> The values that |
Window Table Calcs
For more information, see Table Calculation Functions(Link opens in a new window).
WINDOW_AVG(expression, [start, end])
WINDOW_CORR(expression1, expression2, [start, end])
WINDOW_COUNT(expression, [start, end])
WINDOW_COVAR(expression1, expression2, [start, end])
WINDOW_COVARP(expression1, expression2, [start, end])
WINDOW_MAX(expression, [start, end])
WINDOW_MEDIAN(expression, [start, end])
WINDOW_MIN(expression, [start, end])
WINDOW_PERCENTILE(expression, number, [start, end])
WINDOW_STDEV(expression, [start, end])
WINDOW_STDEVP(expression, [start, end])
WINDOW_SUM(expression, [start, end])
WINDOW_VAR(expression, [start, end])
WINDOW_VARP(expression, [start, end])
X
XPATH functions.
Only supported when connected to Hadoop Hive. For more information, see Pass-Through Functions (RAWSQL)(Link opens in a new window).
XPATH_BOOLEAN(XML string, XPath expression string)
XPATH_DOUBLE(XML string, XPath expression string)
XPATH_FLOAT(XML string, XPath expression string)
XPATH_INT(XML string, XPath expression string)
XPATH_LONG(XML string, XPath expression string)
XPATH_SHORT(XML string, XPath expression string)
XPATH_STRING(XML string, XPath expression string)
Y
YEAR
Syntax | YEAR(date) |
Output | Integer |
Definition | Returns the year of the given <date> as an integer. |
Example | YEAR(#1986-03-25#) = 1,986 |
Notes | See also DAY , WEEK , MONTH , Quarter and the ISO equivalents |
Z
ZN
Syntax | ZN(expression) |
Output | Depends on the data type of the <expression> , or 0. |
Definition | Returns <expression> if it isn't null; otherwise returns zero. |
Example | ZN([Test Grade]) "If the test grade isn't null, return its value. If the test grade is null, return 0." |
Notes |
See also ISNULL. |