To create a two-variable data table to perform what-if analysis in Excel 2010, you enter two ranges of possible input values for the same formula: a range of values for the Row Input Cell in the Data Table dialog box across the first row of the table and a range of values for the Column Input Cell in the dialog box
down the first column of the table. You then enter the formula (or a copy of it) in the cell located at the intersection of this row and column of input values. The steps below for creating a two-variable data table follow a specific example (rather than using generic steps) to help you understand exactly how to use this feature. The following figure shows a Sales Projections worksheet in which two variables are used in calculating the projected sales for the year 2011: a growth rate as a
percentage of increase over last year's sales (in cell B3) and expenses calculated as a percentage of last year's sales (in cell B4). The formula in cell B5 is: =B2+(B2*B3)-(B2*B4).
Sales projection worksheet with a series of possible growth and expense percentages to be plugged into a two-variable data table.
The column of possible growth rates ranging from 1% to 5.5% is entered down column B in the range B8:B17, and a row of possible expenses percentages is entered in the range C7:F7. Follow these steps to complete the two-variable data table for this example:
Copy the original formula entered in cell B5 into cell B7 by typing = (equal to) and then clicking cell B5.
For a two-variable data table, the copy of the original formula must be entered at the intersection of the row and column input values.
Select the cell range B7:F17.
The range of the data table includes the formula along with the various growth rates.
Choose What-If Analysis→Data Table in the Data Tools group on the Data tab.
Excel opens the Data Table dialog box with the insertion point in the Row Input Cell text box.
Click cell B4 to enter the absolute cell address, $B$4, in the Row Input Cell text box.
Click the Column Input Cell text box and then click cell B3 to enter the absolute cell address, $B$3, in this text box.
Click OK.
Excel fills the blank cells of the data table with a TABLE formula using B4 as the Row Input Cell and B3 as the Column Input Cell.
Sales projection worksheet after creating the two-variable data table in the range C8:F17.
The array formula {=TABLE(B4,B3)} that Excel creates for the two-variable data table in this example specifies both a Row Input Cell argument (B4) and a Column Input Cell argument (B3). Because this single array formula is entered into the entire data table range of C8:F17, keep in mind that any editing (in terms of moving or deleting) is restricted to the entire range.
About This Article
This article is from the book:
- Excel 2010 For Dummies ,
About the book author:
Greg Harvey, PhD, is president of Mind Over Media, Inc. He is the author of all editions of Excel For Dummies, Excel All-in-One For Dummies, Excel Workbook For Dummies, and Windows For Dummies Quick Reference. He's also an experienced educator.
This article can be found in the category:
- Excel ,
The Tableau functions in this reference are organized alphabetically. Click a letter to see functions that start with it. If no functions start with that letter, the functions that start with the next letter in the alphabet are shown. You can also press Ctrl+F (Command-F on a Mac) to open a search box that you can use to search the page 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
ABS(number)
Returns the absolute value of the given number.
ExamplesABS(-7) = 7
ABS([Budget Variance])
The second example 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.
ExampleACOS(-1) = 3.14159265358979
AREA(geometry, 'units')
Returns the total surface area of a spatial polygon.
Supported unit names: meters ("meters," "metres" "m"), kilometers ("kilometers," "kilometres," "km"), miles ("miles" or "mi"), feet ("feet," "ft").
ExampleAREA([Geometry], 'km')
ASCII(string)
Return the ASCII code for the first character of string.
ExampleASCII('A') = 65
ASIN(number)
Returns the arc sine of a given number. The result is in radians.
ExampleASIN(1) = 1.5707963267949
ATAN(number)
Returns the arc tangent of a given number. The result is in radians.
ExampleATAN(180) = 1.5652408283942
ATAN2(y number, x number)
Returns the arc tangent of two given numbers (x and y). The result is in radians.
ExampleATAN2(2, 1) = 1.10714871779409
ATTR(expression)
Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored.
AVG(expression)
Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.
BUFFER(geometry, number, 'units')
Returns distance measurement between two points in a specified unit.
Supported unit names: meters ("meters," "metres" "m"), kilometers ("kilometers," "kilometres," "km"), miles ("miles" or "mi"), feet ("feet," "ft").
This function can only be created with a live connection and will continue to work when a data source is converted to an extract.
ExampleBUFFER(MAKEPOINT(47.59, -122.32), 5, 'km')
CASE
CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END
Use the CASE function to perform logical tests and return appropriate values. CASE is often easier to use than IIF or IF THEN ELSE. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches expression is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned.
ExamplesCASE [Region] WHEN 'West' THEN 1 WHEN 'East' THEN 2 ELSE 3 END
CASE LEFT(DATENAME('weekday',[Order Date]),3) WHEN 'Sun' THEN 0 WHEN 'Mon' THEN 1 WHEN 'Tue' THEN 2 WHEN 'Wed' THEN 3 WHEN 'Thu' THEN 4 WHEN 'Fri' THEN 5 WHEN 'Sat' THEN 6 END
CEILING(number)
Rounds a number to the nearest integer of equal or greater value.
ExampleCEILING(3.1415) = 4
Availability by data source
- Microsoft Excel
- Text File
- Statistical File
- Tableau Server
- Amazon EMR Hadoop Hive
- Cloudera Hadoop
- DataStax Enterprise
- Google Analytics
- Google BigQuery
- Hortonworks Hadoop HIve
- MapR Hadoop Hive
- Salesforce
- Spark SQL
CHAR(number)
Returns the character encoded by the ASCII code number.
ExampleCHAR(65) = 'A'
COLLECT (spatial)
An aggregate calculation that combines the values in the argument field. Null values are ignored.
Note: The COLLECT function can only be used with spatial fields.
ExampleCOLLECT ([Geometry])
CONTAINS(string, substring)
Returns true if the given string contains the specified substring.
ExampleCONTAINS("Calculation", "alcu") = true
CORR(expression 1, expression2)
Returns the Pearson correlation coefficient of two expressions.
The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship.
Availability by data source
- Tableau data extracts (you can create an extract from any data source)
- Cloudera Hive
- EXASOL
- Firebird (version 3.0 and later)
- Google BigQuery
- Hortonworks Hadoop Hive
- Oracle
- PostgreSQL
- Presto
- SybaseIQ
- Teradata
- Vertica
For other data sources, consider either extracting the data or using WINDOW_CORR. See Table Calculation Functions.
ExampleYou can use CORR to visualize correlation in a disaggregated scatter plot. The way to do this is to use a table-scoped level of detail expression. For example:
{CORR(Sales, Profit)}
With a level of detail expression, the correlation is run over all rows. If you used a formula like CORR(Sales, Profit) (without the surrounding brackets to make it a level of detail expression), the view would show the correlation of each individual point in the scatter plot with each other point, which is undefined.
See Table-Scoped
COS(number)
Returns the cosine of an angle. Specify the angle in radians.
ExampleCOS(PI( ) /4) = 0.707106781186548
COT(number)
Returns the cotangent of an angle. Specify the angle in radians.
ExampleCOT(PI( ) /4) = 1
COUNT(expression)
Returns the number of items in a group. Null values are not counted.
COUNTD(expression)
Returns the number of distinct items in a group. Null values are not counted. This function is not available in the following cases: workbooks created before Tableau Desktop 8.2 that use Microsoft Excel or text file data sources, workbooks that use the legacy connection, and workbooks that use Microsoft Access data sources. Extract your data into an extract file to use this function. See Extract Your Data.
COVAR(expression 1, expression2)
Returns the sample covariance of two expressions.
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 normalize the covariance calculation, rather than n, which is used by the population covariance (available with the COVARP function). Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population.
Availabiltiy by data source
- Tableau data extracts (you can create an extract from any data source)
- Cloudera Hive
- EXASOL
- Firebird (version 3.0 and later)
- Google BigQuery
- Hortonworks Hadoop Hive
- IBM PDA (Netezza)
- Oracle
- PostgreSQL
- Presto
- SybaseIQ
- Teradata
- Vertica
For other data sources, consider either extracting the data or using WINDOW_COVAR. See Table Calculation Functions.
If expression1 and expression2 are the same—for example, COVAR([profit], [profit])—COVAR returns a value that indicates how widely values are distributed.
Note: The value of COVAR(X, X) is equivalent to the value of VAR(X) and also to the value of STDEV(X)^2.
ExampleThe following formula returns the sample covariance of Sales and Profit.
COVAR([Sales], [Profit])
COVARP(expression 1, expression2)
Returns the population covariance of two expressions.
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 COVAR function) is appropriate.
Availability by data source
- Tableau data extracts (you can create an extract from any data source)
- Cloudera Hive
- EXASOL
- Firebird (version 3.0 and later)
- Google BigQuery
- Hortonworks Hadoop Hive
- IBM PDA (Netezza)
- Oracle
- PostgreSQL
- Presto
- SybaseIQ
- Teradata
- Vertica
For other data sources, consider either extracting the data or using WINDOW_COVARP. See Table Calculation Functions.
If expression1 and expression2 are the same—for example, COVARP([profit], [profit])—COVARP returns a value that indicates how widely values are distributed.
Note: The value of COVARP(X, X) is equivalent to the value of VARP(X) and also to the value of STDEVP(X)^2.
ExampleThe following formula returns the population covariance of Sales and Profit.
COVARP([Sales], [Profit])
DATE(expression)
Returns a date given a number, string, or date expression.
ExamplesDATE([Employee Start Date])
DATE("April 15, 2004") = #April 15, 2004#
DATE("4/15/2004")
DATE(#2006-06-15 14:52#) = #2006-06-15#
Quotation marks are required in the second and third examples.
DATEADD(date_part, interval, date)
Returns the specified date with the specified number interval added to the specified date_part of that date.
ExampleDATEADD('month', 3, #2004-04-15#) = 2004-07-15 12:00:00 AM
This expression adds three months to the date #2004-04-15#.
DATEDIFF(date_part, date1, date2, [start_of_week])
Returns the difference between date1 and date2 expressed in units of date_part.
The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.
ExampleDATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday')= 1
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday')= 0
The first expression returns 1 because when start_of_week is 'monday', then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when start_of_week is 'sunday' then 22 September (a Sunday) and 24 September (a Tuesday) are in the same week.
DATENAME(date_part, date, [start_of_week])
Returns date_part of date as a string. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.
ExamplesDATENAME('year', #2004-04-15#) = "2004"
DATENAME('month', #2004-04-15#) = "April"
DATEPARSE(format, string)
Converts a string to a datetime in the specified format. Support for some locale-specific formats is determined by the computer's system settings. Letters that appear in the data and do not need to be parsed should be surrounded by single quotes (' '). For formats that do not have delimiters between values (for example, MMddyy), verify that they are parsed as expected. The format must be a constant string, not a field value. This function returns Null if the data does not match the format.
This function is available for several connectors. For more information, see Convert a Field to a Date Field.
ExamplesDATEPARSE ("dd.MMMM.yyyy", "15.April.2004") = #April 15, 2004#
DATEPARSE ("h'h' m'm' s's'", "10h 5m 3s") = #10:05:03#
DATEPART(date_part, date, [start_of_week])
Returns date_part of date as an integer.
The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.
Note: When the date_part is weekday, the start_of_week parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets.
ExamplesDATEPART('year', #2004-04-15#) = 2004
DATEPART('month', #2004-04-15#) = 4
DATETIME(expression)
Returns a datetime given a number, string, or date expression.
ExampleDATETIME(“April 15, 2005 07:59:00”) = April 15, 2005 07:59:00
DATETRUNC(date_part, date, [start_of_week])
Truncates the specified 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. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.
ExamplesDATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM
DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM
DAY(date)
Returns the day of the given date as an integer.
ExampleDAY(#2004-04-12#) = 12
DEGREES(number)
Converts a given number in radians to degrees.
ExampleDEGREES(PI( )/4) = 45.0
DISTANCE(Geometry1, Geometry2, "Units")
Returns distance measurement between two points in a specified unit. Supported unit names: meters ("meters," "metres" "m), kilometers ("kilometers," "kilometres," "km"), miles ("miles" or "miles"), feet ("feet," "ft").
This function can only be created with a live connection and will continue to work when a data source is converted to an extract.
ExamplesDISTANCE ({ EXCLUDE [Branch Name] : COLLECT([Selected Point]) }, [unselected point], 'km')
DISTANCE([Origin MakePoint],[Destination MakePoint], "miles")
DIV(integer1, integer2)
Returns the integer part of a division operation, in which integer1 is divided by integer2.
ExampleDIV(11,2) = 5
DOMAIN(string_url)
Note : Supported only when connected to Google BigQuery
Given a URL string, returns the domain as a string.
ExampleDOMAIN('//www.google.com:80/index.html') = 'google.com'
ELSE
See IF THEN ELSE.
ELSEIF
See IF THEN ELSE.
END
Used with functions like IF and CASE to indicate the end of the series of expressions.
ENDSWITH(string, substring)
Returns true if the given string ends with the specified substring. Trailing white spaces are ignored.
ExampleENDSWITH(“Tableau”, “leau”) = true
EXP(number)
Returns e raised to the power of the given number.
ExamplesEXP(2) = 7.389
EXP(-[Growth Rate]*[Time])
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.
ExamplesFIND("Calculation", "alcu") = 2
FIND("Calculation", "Computer") = 0
FIND("Calculation", "a", 3) = 7
FIND("Calculation", "a", 2) = 2
FIND("Calculation", "a", 8) = 0
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.
Note: FINDNTH is not available for all data sources.
ExampleFINDNTH("Calculation", "a", 2) = 7
FIRST( )
Returns the number of rows from the current row to the first row in the partition. For example, the view below shows quarterly sales. When FIRST() is computed within the Date partition, the offset of the first row from the second row is -1.
When the current row index is 3, FIRST() = -2.
FLOAT(expression)
Casts its argument as a floating point number.
ExamplesFLOAT(3) = 3.000
FLOAT([Age]) converts every value in the Age field to a floating point number.
FLOOR(number)
Rounds a number to the nearest integer of equal or lesser value.
ExampleFLOOR(3.1415) = 3
Availability by data source
Data Source | Support |
Microsoft Access | Not supported |
Microsoft Excel | Supported |
Text File | Supported |
Statistical File | Supported |
Tableau Server | Supported |
Actian Vector | Not supported |
Amazon Aurora | Not supported |
Amazon EMR Hadoop Hive | Supported |
Amazon Redshift | Not supported |
Aster Database | Not supported |
Cloudera Hadoop | Supported |
DataStax Enterprise | Supported |
EXASOL | Not supported |
Firebird | Not supported |
Google Analytics | Supported |
Google BigQuery | Supported |
Google Cloud SQL | Not supported |
Hortonworks Hadoop Hive | Supported |
IBM BigInsights | Not supported |
IBM DB2 | Not supported |
IBM Netezza | Not supported |
MapR Hadoop Hive | Supported |
MarkLogic | Not supported |
Microsoft Analysis Services | Not supported |
Microsoft PowerPivot | Not supported |
Microsoft SQL Server | Not supported |
MySQL | Not supported |
Oracle | Not supported |
Oracle Essbase | Not supported |
ParAccel | Not supported |
Pivotal Greenplum | Not supported |
PostgreSQL | Not supported |
Progress OpenEdge | Not supported |
Salesforce | Supported |
SAP HANA | Not supported |
SAP Sybase ASE | Not supported |
SAP Sybase IQ | Not supported |
Spark SQL | Supported |
Splunk | Not supported |
Teradata | Not supported |
Teradata OLAP Connector | Not supported |
Vertica | Not supported |
FULLNAME( )
Returns the full name for the current user. This is the Tableau Server or Tableau Cloud full name when the user is signed in; otherwise the local or network full name for the Tableau Desktop user.
Example[Manager]=FULLNAME( )
If manager Dave Hallsten is signed in, this example returns True only if the Manager field in the view contained Dave Hallsten. When used as a filter, this calculated field can be used to create a user filter that only shows data that is relevant to the person signed in to the server.
GET_JSON_OBJECT(JSON string, JSON path)
Note: Supported only when connected to Hadoop Hive.
Returns the JSON object within the JSON string based on the JSON path.
GROUP_CONCAT(expression)
Note : Supported only when connected to Google BigQuery
Concatenates values from each record into a single comma-delimited string. This function acts like a SUM() for strings.
ExampleGROUP_CONCAT(Region) = "Central,East,West"
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.
HEXBINX and HEXBINY are binning and plotting functions for hexagonal bins. Hexagonal bins are an efficient and elegant option for visualizing data in an x/y plane such as a map. Because the bins are hexagonal, each bin closely approximates a circle and minimizes variation in the distance from the data point to the center of the bin. This makes the clustering both more accurate and informative.
ExampleHEXBINX([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.
ExampleHEXBINY([Longitude], [Latitude])
HOST(string_url)
Note : Supported only when connected to Google BigQuery
Given a URL string, returns the host name as a string.
ExampleHOST('//www.google.com:80/index.html') = 'www.google.com:80'
IF THEN ELSE
IF test THEN value END / IF test THEN value ELSE else END
Use the IF THEN ELSE function to perform logical tests and return appropriate values. The IF THEN ELSE function evaluates a sequence of test conditions and returns the value for the first condition that is true. If no condition is true, the ELSE value is returned. Each test must be a boolean: either be a boolean field in the data source or the result of a logical expression. The final ELSE is optional, but if it is not provided and there is no true test expression, then the function returns Null. All of the value expressions must be of the same type.
ExamplesIF [Cost]>[Budget Cost] THEN 'Over Budget' ELSE 'Under Budget' END
IF [Budget Sales]!=0 THEN [Sales]/[Budget Sales] END
IF THEN ELSEIF
IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END
Use this version of the IF function to perform logical tests recursively. There is no built-in limit to the number of ELSEIF values you can use with an IF function, though individual databases may impose a limit on IF function complexity. While an IF function can be rewritten as a series of nested IIF statements, there are differences in how the expressions will be evaluated. In particular, an IIF statement distinguishes TRUE, FALSE and UNKNOWN, whereas an IF statement only worries about TRUE and not true (which includes both FALSE and UNKNOWN).
Example
When you create bins from a measure, Tableau creates bins of equal size by default. For example, say you have a measure that represents age. When you create bins from that measure, Tableau makes all the bins of equal size. You can specify how big you want the bins to be, but you cannot specify a separate range of values for each bin. A way around this constraint is to create a calculated field to define bins. Then you can create one bin for ages 0 - 20, another for ages 21 - 32, and so on. The following procedure shows how you could do that.
Create a new calculated field by choosing Analysis > Create Calculated Field.
Name the field Age Groups and type the following in the definition area
IF [Age] < 21 THEN 'Under 21' ELSEIF [Age] <= 32 THEN '21-32' ELSEIF [Age] <= 42 THEN '33-42' ELSEIF [Age] <= 52 THEN '43-52' ELSEIF [Age] <= 64 THEN '53-64' ELSE '65+' ENDConfirm that the status message indicates that the formula is valid, and then click OK.
From the Measures area of the Data pane, drag Number of Records to Rows.
From the Dimensions area of the Data pane, drag Age Groups to Columns.
The records are now divided among the six bins that you defined:
Unfortunately, the Under 21 bin is at far right, when you would expect it to be at far left. Tableau's smart enough to put the bins with entirely numerical names in the right order, but it can't guess that the bin name beginning with 'Under' belongs at the left. Fix the problem with a manual sort.
Click the down arrow at the right side of the Age Groups field on Columns and then click Sort. Choose Manual and then move the Under 21 bin up to the top of the list:
Your view is now complete.
IIF(test, then, else, [unknown])
Use the IIF function to perform logical tests and return appropriate values. The first argument, test, must be a boolean: either a boolean field in the data source, or the result of a logical expression using operators (or a logical comparison of AND, OR, or NOT). If test evaluates to TRUE, then IIF returns the then value. If test evaluates to FALSE, then IIF returns the else value.
A boolean comparison may also yield the value UNKNOWN (neither TRUE nor FALSE), usually due to the presence of Null values in test. The final argument to IIF is returned in the event of an UNKNOWN result for the comparison. If this argument is left out, Null is returned.
ExamplesIIF(7>5, 'Seven is greater than five', 'Seven is less than five')
IIF([Cost]>[Budget Cost], 'Over Budget', 'Under Budget')
IIF([Budget Sales]!=0,[Sales]/[Budget Sales],0)
IIF(Sales>=[Budget Sales], 'Over Cost Budget and Over Sales Budget', 'Over Cost Budget and Under Sales Budget','Under Cost Budget')
IFNULL(expression1, expression2)
The IFNULL function returns the first expression if the result is not null, and returns the second expression if it is null.
ExampleIFNULL([Profit], 0) = [Profit]
<expression1> IN <expression2>
Returns TRUE if <expression1> matches any value in <expression2>.
Example[Name] IN [Set of attendees]
SUM([Cost]) IN (19.99, 20.99, 21.99)
INDEX( )
Returns the index of the current row in the partition, without any sorting with regard to value. The first row index starts at 1. For example, the table below shows quarterly sales. When INDEX() is computed within the Date partition, the index of each row is 1, 2, 3, 4..., etc.
For the third row in the partition, INDEX() = 3.
INT(expression)
Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.
ExamplesINT(8.0/3.0) = 2
INT(4.0/1.5) = 2
INT(0.50/1.0) = 0
INT(-9.7) = -9
When a string is converted to an integer it is first converted to a float and then rounded.
ISDATE(string)
The ISDATE function returns TRUE if the string argument can be converted to a date and FALSE if it cannot.
ExamplesISDATE('January 1, 2003') = TRUE
ISDATE('Jan 1 2003') = TRUE
ISDATE('1/1/03') = TRUE
ISDATE('Janxx 1 2003') = FALSE
ISFULLNAME(string)
Returns true if the current user's full name matches the specified full name, or false if it does not match. This function uses the Tableau Server or Tableau Cloud full name when the user is signed in; otherwise it uses the local or network full name for the Tableau Desktop user.
ExampleISFULLNAME("Dave Hallsten")
This example returns true if Dave Hallsten is the current user, otherwise it returns false.
ISMEMBEROF(string)
Returns TRUE if the current user is a member of the given group. This uses the logged in Tableau Server or Tableau Cloud site to resolve group membership, otherwise it always returns false.
Example
ISMEMBEROF("All Users")
ISNULL(expression)
The ISNULL function returns TRUE if the expression is Null and FALSE if it is not.
ExampleThe following example uses ISNULL in combination with IIF to replace null values with 0's.
IIF(ISNULL([Sales]), 0,[Sales] )
ISOQUARTER (date)
Returns the ISO8601 week-based quarter of a given date as an integer.
ExampleISOQUARTER(#2022-03-29#) = 1
ISOWEEK (date)
Returns the ISO8601 week-based week of a given date as an integer.
ExampleISOWEEK(#2022-03-29#) = 13
ISOWEEKDAY (date)
Returns the ISO8601 week-based weekday of a given date as an integer.
ExampleISOWEEKDAY(#2022-03-29#) = 2
ISOYEAR (date)
Returns the ISO8601 week-based year of a given date as an integer.
ExampleISOYEAR(#2019-12-31#) = 2020
ISUSERNAME(string)
Returns true if the current user's username matches the specified username, or false if it does not match. This function uses the Tableau Server or Tableau Cloud username when the user is signed in; otherwise it uses the local or network username for the Tableau Desktop user.
ExampleISUSERNAME(“dhallsten”)
This example returns true if dhallsten is the current user; otherwise it returns false.
LAST( )
Returns the number of rows from the current row to the last row in the partition. For example, the table below shows quarterly sales. When LAST() is computed within the Date partition, the offset of the last row from the second row is 5.
When the current row index is 3 of 7, LAST() = 4.
LEFT(string, number)
Returns the left-most number of characters in the string.
ExampleLEFT("Matador", 4) = "Mata"
LEN(string)
Returns the length of the string.
ExampleLEN("Matador") = 7
LN(number)
Returns the natural logarithm of a number. Returns Null if number is less than or equal to 0.
LOG(number [, base])
Returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used.
LOG2(number)
Note : Supported only when connected to Google BigQuery
Returns the logarithm base 2 of a number.
ExampleLOG2(16) = '4.00'
LOOKUP(expression, [offset])
Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST() + n and LAST() - n as part of your offset definition for a target relative to the first/last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.
The view below shows quarterly sales. When LOOKUP (SUM(Sales), 2) is computed within the Date partition, each row shows the sales value from 2 quarters into the future.
LOOKUP(SUM([Profit]), FIRST()+2) computes the SUM(Profit) in the third row of the partition.
LOWER(string)
Returns string, with all characters lowercase.
ExampleLOWER("ProductVersion") = "productversion"
LTRIM(string)
Returns the string with any leading spaces removed.
ExampleLTRIM(" Matador ") = "Matador "
LTRIM_THIS(string, string)
Note : Supported only when connected to Google BigQuery
Returns the first string with any leading occurrence of the second string removed.
ExampleLTRIM_THIS('[-Sales-]','[-') = 'Sales-]'
MAKEDATE(year, month, day)
Returns a date value constructed from the specified year, month, and date.
Available for Tableau Data Extracts. Check for availability in other data sources.
ExampleMAKEDATE(2004, 4, 15) = #April 15, 2004#
MAKEDATETIME(date, time)
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. This function is available only for MySQL-compatible connections (which for Tableau are, in addition to MySQL, Amazon Aurora and Amazon Aurora).
ExamplesMAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#
MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#
MAKELINE(geometry1,geometry2)
MAKELINE(Geometry1,Geometry2)
Generates a line mark between two spatial points; useful for building origin-destination maps.
ExampleMAKELINE(MAKEPOINT(OriginLat],[OriginLong) ),MAKEPOINT([DestinationLat],[DestinationLong] ) )
MAKELINE(Geometry1, Geometry2 )
MAKEPOINT(Latitude,Longitude)
Converts data from latitude and longitude columns into spatial objects. You can use MAKEPOINT to spatially-enable a data source so that it can be joined with a spatial file using a spatial join. To use MAKEPOINT, your data must contain latitude and longitude coordinates.
ExampleMAKEPOINT([AirportLatitude],[AirportLongitude])
MAKEPOINT(Xcoordinate,Ycoordinate,SRID)
Converts data from projected geographic coordinates into spatial objects. SRID is a spatial reference identifier that uses ESPG reference system codes to specify coordinate systems. If SRID is not specified, WGS84 is assumed and parameters are treated as latitude/longitude in degrees.
This function can only be created with a live connection and will continue to work when a data source is converted to an extract.
ExampleMAKEPOINT([Xcoord],[Ycoord],3493)
MAKETIME(hour, minute, second)
Returns a date value constructed from the specified hour, minute, and second.
Available for Tableau Data Extracts. Check for availability in other data sources.
ExampleMAKETIME(14, 52, 40) = #14:52:40#
MAX(a, b)
Returns the maximum of a and b (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, MAX finds the value that is highest in the sort sequence defined by the database for that column. It returns Null if either argument is Null.
ExampleMAX ("Apple","Banana") = "Banana"
MAX(expression) or MAX(expr1, expr2)
Usually applied to numbers but also works on dates. Returns the maximum of a and b (a and b must be of the same type). Returns Null if either argument is Null.
ExamplesMAX(#2004-01-01# ,#2004-03-01#) = 2004-03-01 12:00:00 AM
MAX([ShipDate1], [ShipDate2])
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.
ExamplesMAX(4,7)
MAX(Sales,Profit)
MAX([First Name],[Last Name])
MEDIAN(expression)
Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. This function is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections.
Availability by data source
It is also not available for connections using any of the following data sources:
- Access
- Amazon Redshift
- Cloudera Hadoop
- IBM DB2
- IBM PDA (Netezza)
- Microsoft SQL Server
- MySQL
- SAP HANA
- Teradata
- Vertica
For these data source types, you can extract your data into an extract file to use this function. See Extract Your Data.
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.
ExamplesMID("Calculation", 2) = "alculation"
MID("Calculation", 2, 5) ="alcul"
MIN(a, b)
Returns the minimum of a and b (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, MIN finds the value that is lowest in the sort sequence. It returns Null if either argument is Null.
ExampleMIN ("Apple","Banana") = "Apple"
MIN(expression) or MIN(expr1, expr2)
Usually applied to numbers but also works on dates. Returns the minimum of a and b (a and b must be of the same type). Returns Null if either argument is Null.
ExamplesMIN(#2004-01-01# ,#2004-03-01#) = 2004-01-01 12:00:00 AM
MIN([ShipDate1], [ShipDate2])
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.
ExamplesMIN(4,7)
MIN(Sales,Profit)
MIN([First Name],[Last Name])
MODEL_EXTENSION_BOOL(model_name, arguments, expression)
Returns the Boolean result of model_name, a deployed analytics extension model. Each argument is a single string that defines the elements you use. Use expression to define the input fields that are sent to the model, and use aggregation functions (SUM, AVG, etc.) to aggregate their results. Define and order each input field as its own argument.
ExampleMODEL_EXTENSION_BOOL("model_isProfitable", "[inputSales]", "[inputCosts]", SUM([Sales]), SUM([Costs]))
MODEL_EXTENSION_INT(model_name, arguments, expression)
Returns the integer result of model_name, a deployed analytics extension model. Each argument is a single string that defines the elements you use. Use expression to define the input fields that are sent to the model, and use aggregation functions (SUM, AVG, etc.) to aggregate their results. Define and order each input field as its own argument.
ExampleMODEL_EXTENSION_INT("model_getPopulation", "[inputCity]", "[inputState]", MAX([City]), MAX([State]))
MODEL_EXTENSION_REAL(model_name, arguments, expression)
Returns the numeric result of model_name, a deployed analytics extension model. Each argument is a single string that defines the elements you use. Use expression to define the input fields that are sent to the model, and use aggregation functions (SUM, AVG, etc.) to aggregate their results. Define and order each input field as its own argument.
ExampleMODEL_EXTENSION_REAL("model_ProfitRatio", "[inputSales]", "[inputCosts]", SUM([Sales]), SUM([Costs]))
MODEL_EXTENSION_STR(model_name, arguments, expression)
Returns the string result of model_name, a deployed analytics extension model. Each argument is a single string that defines the elements you use. Use expression to define the input fields that are sent to the model, and use aggregation functions (SUM, AVG, etc.) to aggregate their results. Define and order each input field as its own argument.
ExampleMODEL_EXTENSION_STR("model_mostPopulatedCity", "[inputCountry]", "[inputYear]", MAX([Country]), MAX([Year]))
MODEL_PERCENTILE(target_expression, predictor_expression(s))
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).
ExampleMODEL_PERCENTILE( SUM([Sales]),COUNT([Orders]))
MODEL_QUANTILE(quantile, target_expression, predictor_expression(s))
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.
ExampleMODEL_QUANTILE(0.5, SUM([Sales]),COUNT([Orders]))
MONTH(date)
Returns the month of the given date as an integer.
ExampleMONTH(#2004-04-15#) = 4
NOT
Performs logical negation on an expression
ExampleIF NOT <exp> THEN <then> END
NOW( )
Returns the current date and time.
The return varies depending on the nature of the connection:
For a live, unpublished connection, NOW returns the data source server time.
For a live, published connection, NOW returns the data source server time.
For an unpublished extract, NOW returns the local system time.
For a published extract, NOW returns the local time of the Tableau Server Data Engine. When there are multiple worker machines indifferent time zones, this can produce inconsistent results.
NOW( ) = 2004-04-15 1:08:21 PM
OR
Performs logical disjunction on two expressions
ExampleIF <exp1> or <expr2> THEN <then> END
PARSE_URL(string, url_part)
Note: Supported only when connected to Hadoop Hive and Cloudera Impala.
Returns a component of the given URL string where the component is defined by url_part. Valid url_part values include: 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'AUTHORITY', 'FILE' and 'USERINFO'.
ExamplePARSE_URL('//www.tableau.com', 'HOST') = 'www.tableau.com'
PARSE_URL_QUERY(string, key)
Note: Supported only when connected to Hadoop Hive and Cloudera Impala.
Returns the value of the specified query parameter in the given URL string. The query parameter is defined by the key.
ExamplePARSE_URL_QUERY('//www.tableau.com?page=1&cat=4', 'page') = '1'
PERCENTILE(expression, number)
Returns the percentile value from the given expression corresponding to the specified number. The number must be between 0 and 1 (inclusive)—for example, 0.66, and must be a numeric constant.
Availability by data source
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.
EXASOL 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.
PI( )
Returns the numeric constant pi: 3.14159.
POWER(number, power)
Raises the number to the specified power.
ExamplesPOWER(5,2) = 52 = 25
POWER(Temperature, 2)
You can also use the ^ symbol:
5^2 = POWER(5,2) = 25
PREVIOUS_VALUE(expression)
Returns the value of this calculation in the previous row. Returns the given expression if the current row is the first row of the partition.
ExampleSUM([Profit]) * PREVIOUS_VALUE(1) computes the running product of SUM(Profit).
Quarter
Returns the quarter of a given date as an integer.
ExampleQUARTER(#2021-02-20#) = 1
RADIANS(number)
Converts the given number from degrees to radians.
ExampleRADIANS(180) = 3.14159
RANK(expression, ['asc' | 'desc'])
Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank.
Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.
With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 2, 1).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
ExampleThe following image shows the effect of the various ranking functions (RANK, RANK_DENSE, RANK_MODIFIED, RANK_PERCENTILE, and RANK_UNIQUE) on a set of values. The data set contains information on 14 students (StudentA through StudentN); the Age column shows the current age of each student (all students are between 17 and 20 years of age). The remaining columns show the effect of each rank function on the set of age values, always assuming the default order (ascending or descending) for the function.
RANK_DENSE(expression, ['asc' | 'desc'])
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.
With this function, the set of values (6, 9, 9, 14) would be ranked (3, 2, 2, 1).
RANK_MODIFIED(expression, ['asc' | 'desc'])
Returns the modified competition rank for the current row in the partition.
Identical values are assigned an identical rank.
With this function, the set of values (6, 9, 9, 14) would be ranked (4, 3, 3, 1).
RANK_PERCENTILE(expression, ['asc' | 'desc'])
Returns the percentile rank for the current row in the partition.
With this function, the set of values (6, 9, 9, 14) would be ranked (0.25, 0.75, 0.75, 1.00).
Note: Unlike the other rank options, the default is ascending.
RANK_UNIQUE(expression, ['asc' | 'desc'])
Returns the unique rank for the current row in the partition.
Identical values are assigned different ranks.
With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 3, 1).
RAWSQL_BOOL("sql_expr", [arg1], …[argN])
Returns a Boolean result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In the example, %1 is equal to [Sales] and %2 is equal to [Profit].
RAWSQL_BOOL("IIF(%1 > %2, True, False)", [Sales], [Profit])
RAWSQL_DATE("sql_expr", [arg1], …[argN])
Returns a Date result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In this example, %1 is equal to [Order Date].
RAWSQL_DATE("%1", [Order Date])
RAWSQL_DATETIME("sql_expr", [arg1], …[argN])
Returns a Date and Time result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Delivery Date].
Example
RAWSQL_DATETIME("MIN(%1)", [Delivery Date])
RAWSQL_INT("sql_expr", [arg1], …[argN])
Returns an integer result from a given SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales].
Example
RAWSQL_INT("500 + %1", [Sales])
RAWSQL_REAL("sql_expr", [arg1], …[argN])
Returns a numeric result from a given SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales]
Example
RAWSQL_REAL("-123.98 * %1', [Sales])
RAWSQL_SPATIAL
Returns a Spatial from a given SQL expression that is passed directly to the underlying data source. Use %n in the SQL expression as a substitution syntax for database values.
Example
In this example, %1 is equal to [Geometry].
RAWSQL_SPATIAL("%1", [Geometry])
RAWSQL_STR("sql_expr", [arg1], …[argN])
Returns a string from a given SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Customer Name].
Example
RAWSQL_STR("%1", [Customer Name])
RAWSQLAGG_BOOL("sql_expr", [arg1], …[argN])
Returns a Boolean result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values.
Example
In the example, %1 is equal to [Sales] and %2 is equal to [Profit].
RAWSQLAGG_BOOL("SUM( %1) >SUM( %2)”, [Sales], [Profit])
RAWSQLAGG_DATE(“sql_expr", [arg1], …[argN])
Returns a Date result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Order Date].
Example
RAWSQLAGG_DATE("MAX(%1)", [Order Date])
RAWSQLAGG_DATETIME("sql_expr", [arg1], …[argN])
Returns a Date and Time result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Delivery Date].
Example
RAWSQLAGG_DATETIME("MIN(%1)", [Delivery Date])
RAWSQLAGG_INT(“sql_expr”, [arg1,] …[argN])
Returns an integer result from a given aggregate SQL expression. The SQL expression is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales].
Example
RAWSQLAGG_INT("500 + SUM(%1)", [Sales])
RAWSQLAGG_REAL("sql_expr", [arg1,] …[argN])
Returns a numeric result from a given aggregate SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Sales]
Example
RAWSQLAGG_REAL("SUM( %1)", [Sales])
RAWSQLAGG_STR("sql_expr", [arg1,] …[argN])
Returns a string from a given aggregate SQL expression that is passed directly to the underlying database. Use %n in the SQL expression as a substitution syntax for database values. In this example, %1 is equal to [Discount].
Example
RAWSQLAGG_STR("AVG(%1)", [Discount])
REGEXP_EXTRACT(string, pattern)
Returns the portion of the string that matches the regular expression pattern. This function is available for Text File, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Snowflake, and Oracle data sources.
For Tableau data extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source's documentation. For Tableau extracts, regular expression syntax conforms to the standards of the ICU (International Components for Unicode), an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. See the Regular Expressions(Link opens in a new window) page in the online ICU User Guide.
ExampleREGEXP_EXTRACT('abc 123', '[a-z]+\s+(\d+)') = '123'
REGEXP_EXTRACT_NTH(string, pattern, index)
Returns the portion of the string that matches the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index. If index is 0, the entire string is returned. This function is available for Text File, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), and Oracle data sources.
For Tableau data extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source's documentation. For Tableau extracts, regular expression syntax conforms to the standards of the ICU (International Components for Unicode), an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. See the Regular Expressions(Link opens in a new window) page in the online ICU User Guide.
ExampleREGEXP_EXTRACT_NTH('abc 123', '([a-z]+)\s+(\d+)', 2) = '123'
REGEXP_MATCH(string, pattern)
Returns TRUE if a substring of the specified string matches the regular expression pattern. This function is available for Text File, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Impala 2.3.0 (through Cloudera Hadoop data sources), Snowflake, and Oracle data sources.
For Tableau data extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source's documentation. For Tableau extracts, regular expression syntax conforms to the standards of the ICU (International Components for Unicode), an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. See the Regular Expressions(Link opens in a new window) page in the online ICU User Guide.
ExampleREGEXP_MATCH('-([1234].[The.Market])-','\[\s*(\w*\.)(\w*\s*\])')=true
REGEXP_REPLACE(string, pattern, replacement)
Returns a copy of the given string where the regular expression pattern is replaced by the replacement string. This function is available for Text File, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extract, Microsoft Excel, Salesforce, Vertica, Pivotal Greenplum, Teradata (version 14.1 and above), Snowflake, and Oracle data sources.
For Tableau data extracts, the pattern and the replacement must be constants.
For information on regular expression syntax, see your data source's documentation. For Tableau extracts, regular expression syntax conforms to the standards of the ICU (International Components for Unicode), an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. See the Regular Expressions(Link opens in a new window) page in the online ICU User Guide.
ExampleREGEXP_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.
ExampleREPLACE("Version8.5", "8.5", "9.0") = "Version9.0"
RIGHT(string, number)
Returns the right-most number of characters in string.
ExampleRIGHT("Calculation", 4) = "tion"
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.
ExampleThis example rounds every Sales value to an integer:
ROUND(Sales)
Some databases, such as SQL Server, allow specification of a negative length, where -1 rounds number to 10's, -2 rounds to 100's, and so on. This is not true of all databases. For example, it is not true of Excel or Access.
RTRIM(string)
Returns string with any trailing spaces removed.
ExampleRTRIM(" Calculation ") = " Calculation"
RTRIM_THIS(string, string)
Note : Supported only when connected to Google BigQuery
Returns the first string with any trailing occurrence of the second string removed.
ExampleRTRIM_THIS('[-Market-]','-]') = '[-Market'
RUNNING_AVG(expression)
Returns the running average of the given expression, from the first row in the partition to the current row.
The view below shows quarterly sales. When RUNNING_AVG(SUM([Sales]) is computed within the Date partition, the result is a running average of the sales values for each quarter.
RUNNING_AVG(SUM([Profit])) computes the running average of SUM(Profit).
RUNNING_COUNT(expression)
Returns the running count of the given expression, from the first row in the partition to the current row.
ExampleRUNNING_COUNT(SUM([Profit])) computes the running count of SUM(Profit).
RUNNING_MAX(expression)
Returns the running maximum of the given expression, from the first row in the partition to the current row.
RUNNING_MAX(SUM([Profit])) computes the running maximum of SUM(Profit).
RUNNING_MIN(expression)
Returns the running minimum of the given expression, from the first row in the partition to the current row.
RUNNING_MIN(SUM([Profit])) computes the running minimum of SUM(Profit).
RUNNING_SUM(expression)
Returns the running sum of the given expression, from the first row in the partition to the current row.
RUNNING_SUM(SUM([Profit])) computes the running sum of SUM(Profit)
SCRIPT_BOOL
Returns a Boolean result from the specified expression. The expression is passed directly to a running external service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.).
In Python expressions, use _argn (with a leading underscore).
ExamplesIn this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_BOOL("is.finite(.arg1)", SUM([Profit]))
The next example returns True for store IDs in Washington state, and False otherwise. This example could be the definition for a calculated field titled IsStoreInWA.
SCRIPT_BOOL('grepl(".*_WA", .arg1, perl=TRUE)',ATTR([Store ID]))
A command for Python would take this form:
SCRIPT_BOOL("return map(lambda x : x > 0, _arg1)", SUM([Profit]))
SCRIPT_INT
Returns an integer result from the specified expression. The expression is passed directly to a running external service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
ExamplesIn this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_INT("is.finite(.arg1)", SUM([Profit]))
In the next example, k-means clustering is used to create three clusters:
SCRIPT_INT('result <- kmeans(data.frame(.arg1,.arg2,.arg3,.arg4), 3);result$cluster;', SUM([Petal length]), SUM([Petal width]),SUM([Sepal length]),SUM([Sepal width]))
A command for Python would take this form:
SCRIPT_INT("return map(lambda x : int(x * 5), _arg1)", SUM([Profit]))
SCRIPT_REAL
Returns a real result from the specified expression. The expression is passed directly to a running external service instance. In
R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
ExamplesIn this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_REAL("is.finite(.arg1)", SUM([Profit]))
The next example converts temperature values from Celsius to Fahrenheit.
SCRIPT_REAL('library(udunits2);ud.convert(.arg1, "celsius", "degree_fahrenheit")',AVG([Temperature]))
A command for Python would take this form:
SCRIPT_REAL("return map(lambda x : x * 0.5, _arg1)", SUM([Profit]))
SCRIPT_STR
Returns a string result from the specified expression. The expression is passed directly to a running external service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
ExamplesIn this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_STR("is.finite(.arg1)", SUM([Profit]))
The next example extracts a state abbreviation from a more complicated string (in the original form 13XSL_CA, A13_WA):
SCRIPT_STR('gsub(".*_", "", .arg1)',ATTR([Store ID]))
A command for Python would take this form:
SCRIPT_STR("return map(lambda x : x[:2], _arg1)", ATTR([Region]))
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.
ExampleIf 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.
ExampleSIN(0) = 1.0
SIN(PI( )/4) = 0.707106781186548
SIZE()
Returns the number of rows in the partition. For example, the view below shows quarterly sales. Within the Date partition, there are seven rows so the Size() of the Date partition is 7.
SIZE() = 5 when the current partition contains five rows.
SPACE(number)
Returns a string that is composed of the specified number of repeated spaces.
ExampleSPACE(1) = " "
SPLIT(string, delimiter, token number)
Returns a substring from a string, using a delimiter character to divide the string into a sequence of tokens.
The string is interpreted as an alternating sequence of delimiters and tokens. So for the string abc-defgh-i-jkl, where the delimiter character is '-', the tokens are abc, defgh, i, and jlk. Think of these as tokens 1 through 4. SPLIT returns the token corresponding to the token number. When the token number is positive, tokens are counted starting from the left end of the string; when the token number is negative, tokens are counted starting from the right.
ExamplesSPLIT ('a-b-c-d', '-', 2) = 'b'
SPLIT ('a|b|c|d', '|', -2) = 'c'
Availability by data source
Note: 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 string. The following table shows which data sources support negative token numbers (splitting from the right) and whether there is a limit on the number of splits allow per data source. A SPLIT function that specifies a negative token number and would be legal with other data sources will return this error with these data sources: “Splitting from right is not support by the data source.”
Data Source | Left/Right Constraints | Maximum Number of Splits | Version Limitations |
Tableau Data Extract | Both | Infinite | |
Microsoft Excel | Both | Infinite | |
Text file | Both | Infinite | |
Salesforce | Both | Infinite | |
OData | Both | Infinite | |
Google Analytics | Both | Infinite | |
Tableau Data Server | Both | Infinite | Supported in version 9.0. |
Vertica | Left only | 10 | |
Oracle | Left only | 10 | |
MySQL | Both | 10 | |
PostgreSQL | Left only prior to version 9.0; both for version 9.0 and above | 10 | |
Teradata | Left only | 10 | Version 14 and later |
Amazon Redshift | Left only | 10 | |
Aster Database | Left only | 10 | |
Google BigQuery | Left only | 10 | |
Hortonworks Hadoop Hive | Left only | 10 | |
Cloudera Hadoop | Left only | 10 | Impala supported starting in version 2.3.0. |
Microsoft SQL Server | Both | 10 | 2008 and later |
SQRT(number)
Returns the square root of a number.
ExampleSQRT(25) = 5
SQUARE(number)
Returns the square of a number.
Example
SQUARE(5) = 25
STARTSWITH(string, substring)
Returns true if string starts with substring. Leading white spaces are ignored.
ExampleSTARTSWITH(“Joker”, “Jo”) = true
STDEV(expression)
Returns the statistical standard deviation of all values in the given expression based on a sample of the population.
STDEVP(expression)
Returns the statistical standard deviation of all values in the given expression based on a biased population.
STR(expression)
Casts its argument as a string.
ExampleSTR([Age]) takes all of the values in the measure called Age and converts them to strings.
SUM(expression)
Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored.
TAN(number)
Returns the tangent of an angle. Specify the angle in radians..
ExampleTAN(PI ( )/4) = 1.0
THEN
See IF THEN ELSE and CASE .
TIMESTAMP_TO_USEC(expression)
Note : Supported only when connected to Google BigQuery
Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
ExampleTIMESTAMP_TO_USEC(#2012-10-01 01:02:03#)=1349053323000000
TLD(string_url)
Note : Supported only when connected to Google BigQuery
Given a URL string, returns the top level domain plus any country domain in the URL.
ExampleTLD('//www.google.com:80/index.html') = '.com'
TLD('//www.google.co.uk:80/index.html') = '.co.uk'
TODAY( )
Returns the current date.
ExampleTODAY( ) = 2004-04-15
TOTAL(expression)
Returns the total for the given expression in a table calculation partition.
Detailed example
Assume you are starting with this view:
You open the calculation editor and create a new field which you name Totality:
You then drop Totality on Text, to replace SUM(Sales). Your view changes such that it sums values based on the default Compute Using value:
This raises the question, What is the default Compute Using value? If you right-click (Control-click on a Mac) Totality in the Data pane and choose Edit, there is now an additional bit of information available:
The default Compute Using value is Table (Across). The result is that Totality is summing the values across each row of your table. Thus, the value that you see across each row is the sum of the values from the original version of the table.
The values in the 2011/Q1 row in the original table were $8601, $6579, $44262, and $15006. The values in the table after Totality replaces SUM(Sales) are all $74,448, which is the sum of the four original values.
Notice the triangle next to Totality after you drop it on Text:
This indicates that this field is using a table calculation. You can right-click the field and choose Edit Table Calculation to redirect your function to a different Compute Using value. For example, you could set it to Table (Down). In that case, your table would look like this:
TRIM(string)
Returns the string with leading and trailing spaces removed. For example, TRIM(" Calculation ") = "Calculation"
UPPER(string)
Returns string, with all characters uppercase.
ExampleUPPER("Calculation") = "CALCULATION"
USEC_TO_TIMESTAMP(expression)
Note : Supported only when connected to Google BigQuery
Converts a UNIX timestamp in microsseconds to a TIMESTAMP data type.
ExampleUSEC_TO_TIMESTAMP(1349053323000000) = #2012-10-01 01:02:03#
USERDOMAIN()
Returns the domain for the current user when the user is signed on to Tableau Server. Returns the Windows domain if the Tableau Desktop user is on a domain. Otherwise this function returns a null string.
Example[Manager]=USERNAME() AND [Domain]=USERDOMAIN()
USERNAME( )
Returns the username for the current user. This is the Tableau Server or Tableau Cloud username when the user is signed in; otherwise it is the local or network username for the Tableau Desktop user.
Example[Manager]=USERNAME( )
If the manager dhallsten was signed in, this function would only return True when the Manager field in the view is dhallsten. When used as a filter this calculated field can be used to create a user filter that only shows data that is relevant to the person signed in to the server.
VAR(expression)
Returns the statistical variance of all values in the given expression based on a sample of the population.
VARP(expression)
Returns the statistical variance of all values in the given expression on the entire population.
WEEK(date)
Returns the week of a given date as an integer.
ExampleWEEK(#2022-03-29#) = 14
WHEN
See CASE
WINDOW_AVG(expression, [start, end])
Returns the average of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window average within the Date partition returns the average sales across all dates.
WINDOW_AVG(SUM([Profit]), FIRST()+1, 0) computes the average of SUM(Profit) from the second row to the current row.
WINDOW_CORR(expression1, expression2, [start, end])
Returns the Pearson correlation coefficient of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If start and end are omitted, the entire partition is used.
The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship.
There is an equivalent aggregation fuction: CORR.
ExampleThe following formula returns the Pearson correlation of SUM(Profit) and SUM(Sales) from the five previous rows to the current row.
WINDOW_CORR(SUM[Profit]), SUM([Sales]), -5, 0)
WINDOW_COUNT(expression, [start, end])
Returns the count of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_COUNT(SUM([Profit]), FIRST()+1, 0) computes the count of SUM(Profit) from the second row to the current row
WINDOW_COVAR(expression1, expression2, [start, end])
Returns the sample covariance of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end arguments are omitted, the window is the entire partition.
Sample covariance uses the number of non-null data points n - 1 to normalize the covariance calculation, rather than n, which is used by the population covariance (with the WINDOW_COVARP function). Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population.
There is an equivalent aggregation fuction: COVAR.
ExampleThe following formula returns the sample covariance of SUM(Profit) and SUM(Sales) from the two previous rows to the current row.
WINDOW_COVAR(SUM([Profit]), SUM([Sales]), -2, 0)
WINDOW_COVARP(expression1, expression2, [start, end])
Returns the population covariance of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If start and end are omitted, the entire partition is used.
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 WINDOW_COVAR function) is appropriate.
There is an equivalent aggregation fuction: COVARP. See Tableau Functions (Alphabetical)(Link opens in a new window).
ExampleThe following formula returns the population covariance of SUM(Profit) and SUM(Sales) from the two previous rows to the current row.
WINDOW_COVARP(SUM([Profit]), SUM([Sales]), -2, 0)
WINDOW_MAX(expression, [start, end])
Returns the maximum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window maximum within the Date partition returns the maximum sales across all dates.
WINDOW_MAX(SUM([Profit]), FIRST()+1, 0) computes the maximum of SUM(Profit) from the second row to the current row.
WINDOW_MEDIAN(expression, [start, end])
Returns the median of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly profit. A window median within the Date partition returns the median profit across all dates.
WINDOW_MEDIAN(SUM([Profit]), FIRST()+1, 0) computes the median of SUM(Profit) from the second row to the current row.
WINDOW_MIN(expression, [start, end])
Returns the minimum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window minimum within the Date partition returns the minimum sales across all dates.
WINDOW_MIN(SUM([Profit]), FIRST()+1, 0) computes the minimum of SUM(Profit) from the second row to the current row.
WINDOW_PERCENTILE(expression, number, [start, end])
Returns the value corresponding to the specified percentile within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_PERCENTILE(SUM([Profit]), 0.75, -2, 0) returns the 75th percentile for SUM(Profit) from the two previous rows to the current row.
WINDOW_STDEV(expression, [start, end])
Returns the sample standard deviation of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_STDEV(SUM([Profit]), FIRST()+1, 0) computes the standard deviation of SUM(Profit) from the second row to the current row.
WINDOW_STDEVP(expression, [start, end])
Returns the biased standard deviation of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_STDEVP(SUM([Profit]), FIRST()+1, 0) computes the standard deviation of SUM(Profit) from the second row to the current row.
WINDOW_SUM(expression, [start, end])
Returns the sum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window sum computed within the Date partition returns the summation of sales across all quarters.
WINDOW_SUM(SUM([Profit]), FIRST()+1, 0) computes the sum of SUM(Profit) from the second row to the current row.
WINDOW_VAR(expression, [start, end])
Returns the sample variance of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_VAR((SUM([Profit])), FIRST()+1, 0) computes the variance of SUM(Profit) from the second row to the current row.
WINDOW_VARP(expression, [start, end])
Returns the biased variance of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
ExampleWINDOW_VARP(SUM([Profit]), FIRST()+1, 0) computes the variance of SUM(Profit) from the second row to the current row.
XPATH_BOOLEAN(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns true if the XPath expression matches a node or evaluates to true.
ExampleXPATH_BOOLEAN('<values> <value id="0">1</value><value id="1">5</value>', 'values/value[@id="1"] = 5') = true
XPATH_DOUBLE(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the floating-point value of the XPath expression.
ExampleXPATH_DOUBLE('<values><value>1.0</value><value>5.5</value> </values>', 'sum(value/*)') = 6.5
XPATH_FLOAT(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the floating-point value of the XPath expression.
ExampleXPATH_FLOAT('<values><value>1.0</value><value>5.5</value> </values>','sum(value/*)') = 6.5
XPATH_INT(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.
ExampleXPATH_INT('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6
XPATH_LONG(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.
ExampleXPATH_LONG('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6
XPATH_SHORT(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the numerical value of the XPath expression, or zero if the XPath expression cannot evaluate to a number.
ExampleXPATH_SHORT('<values><value>1</value><value>5</value> </values>','sum(value/*)') = 6
XPATH_STRING(XML string, XPath expression string)
Note: Supported only when connected to Hadoop Hive
Returns the text of the first matching node.
ExampleXPATH_STRING('<sites ><url domain="org">//www.w3.org</url> <url domain="com">//www.tableau.com</url></sites>', 'sites/url[@domain="com"]') = '//www.tableau.com'
YEAR (date)
Returns the year of the given date as an integer.
ExampleYEAR(#2004-04-15#) = 2004
ZN(expression)
Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.
ExampleZN([Profit]) = [Profit]
Want to learn more about functions?
Read the functions topics(Link opens in a new window).
See also
Tableau Functions (by Category)(Link opens in a new window)
Functions in Tableau(Link opens in a new window)