Calculated field tableau

Calculated field tableau DEFAULT

Tips for Working with Calculated Fields in Tableau

To help you become more efficient with creating and editing calculated fields in Tableau, this article lists several tips for working in the calculation editor.

Drag and drop fields into the calculation editor

When creating fields in the calculation editor, you can drag existing fields from the Data pane into the editor at any time.

Drag and drop formulas from the calculation editor to the Data pane

When typing a calculation in the calculation editor, you can highlight all or part of the formula and drag it to the Data pane to create a new calculated field. You can then rename the field by typing a name. For more information, see Ad-Hoc Calculations(Link opens in a new window).

Use the functions reference in the calculation editor

When typing a calculation in the calculation editor, you can use the functions reference to browse all the functions available in Tableau.

To open the functions reference:

  • In the calculation editor, click the triangle icon on the right-side of the editor.

To add a function from the reference to a formula:

  • In the function reference, double-click a function.

Take advantage of auto-complete for formulas

As you type a formula in the calculation editor, Tableau suggests options to complete items in your formula. Tableau suggests functions, fields in your data source, parameters, sets, and bins that begin with or contain the string you type. The list of suggestions update as you type.

To add an item from auto-complete to a formula:

  • Press Enter on your keyboard to select the highlighted suggestion.

    Note: You can use the up and down arrows on your keyboard to move between items in the auto-complete list.

Drag table calculations into the calculation editor to edit them

When you create a table calculation, you can drag it into the calculation editor to review or make changes to the formula.

To edit a table calculation in the calculation editor:

  1. Click the Data pane drop-down and select Create Calculated Field.

  2. From the worksheet, drag the table calculation into the calculation editor.

  3. When finished, click OK.

Resize text in the calculation editor

You can adjust the size of the text in the calculation editor as you create or edit calculations.

To increase text size in the calculation editor:

  • Press the and keys on your keyboard ( on a Mac)

To decrease text size in the calculation editor:

  • Press the and keys on your keyboard ( on a Mac).

Note: Text size persists until you close the editor. The next time you open the editor, text is at the default size.

See which sheets are using a calculated field

As you edit a calculated field, you can click Sheets Affected to see which other sheets are using the field. These sheets will also be updated when you commit your changes.

Sours: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_tips.htm

Get Started with Calculations in Tableau

This article describes how to create and use calculated fields in Tableau using an example.

You'll learn Tableau calculation concepts, as well as how to create and edit a calculated field. You will also learn how to work with the calculation editor, and use a calculated field in the view.

If you're new to Tableau calculations or to creating calculated fields in Tableau, this is a good place to start.

Why Use Calculated Fields

Calculated fields allow you to create new data from data that already exists in your data source. When you create a calculated field, you are essentially creating a new field (or column) in your data source, the values or members of which are determined by a calculation that you control. This new calculated field is saved to your data source in Tableau, and can be used to create more robust visualisations. But don't worry: your original data remains untouched.

You can use calculated fields for many, many reasons. Some examples might include:

  • To segment data
  • To convert the data type of a field, such as converting a string to a date.
  • To aggregate data
  • To filter results
  • To calculate ratios

Types of calculations

You create calculated fields using calculations. There are three main types of calculations you can use to create calculated fields in Tableau:

  • Basic calculations - Basic calculations allow you to transform values or members at the data source level of detail (a row-level calculation) or at the visualisation level of detail (an aggregate calculation).

  • Level of Detail (LOD) expressions - Just like basic calculations, LOD calculations allow you to compute values at the data source level and the visualisation level. However, LOD calculations give you even more control on the level of granularity you want to compute. They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED) with respect to the granularity of the visualisation.

    For more information, see Create Level of Detail Expressions in Tableau(Link opens in a new window).

  • Table calculations - Table calculations allow you to transform values at the level of detail of the visualisation only. For more information, see Transform Values with Table Calculations(Link opens in a new window).

The type of calculation you choose depends on the needs of your analysis and the question you want to answer.

Create a calculated field

Once you have determined the type of calculation you want to use, it's time to create a calculated field. This example uses a basic calculation.

Note: The example in this article uses the Sample-Superstore data source that comes with Tableau Desktop. To follow along with the steps in this article, connect to the Sample-Superstore saved data source and navigate to Sheet 1.

  1. In Tableau, select Analysis > Create Calculated Field.

  2. In the Calculation Editor that opens, do the following:

    • Enter a name for the calculated field. In this example, the field is called, Discount Ratio.

    • Enter a formula. This example uses the following formula:

      This formula checks if sales is not equal to zero. If true, it returns the discount ratio (Discount/Sales); if false, it returns zero.

      Tip:

      To see a list of available functions, click the triangle icon on the right-side of the Calculation Editor.

      Each function includes syntax, a description, and an example for your reference.

      Double-click a function in the list to add it to the formula.

      For more tips, see Tips for Working with Calculated Fields in Tableau.

  3. When finished, click OK.

    The new calculated field is added to Measures in the Data pane because it returns a number. An equal sign (=) appears next to the data type icon. All calculated fields have equal signs (=) next to them in the Data pane.

Use a calculated field in the view

Step 1: Build the view

  1. From Dimensions, drag Region to the Columns shelf.

  2. From Dimensions, drag Category to the Rows shelf.

  3. On the Rows shelf, click the plus icon (+) on the Category field to drill-down to Subcategory.

    The view updates to look like this:

Step 2: Add the calculated field to the view

  1. From Measures, drag Discount Ratio to Colour on the Marks card.

    The view updates to highlight table.

    You can see that Binders are heavily discounted in the Central region. Notice that Discount Ratio is automatically aggregated as a sum.

  2. On the Rows shelf, right-click SUM(Discount Ratio) and select Measure (Sum) > Average.

    The view updates with the average of discount ratio shown.

Edit a Calculated Field

If at any time you need to change a calculation, you can edit the calculated field and it will update across your entire workbook.

To edit a calculated field:

  1. In the Data pane, right-click the calculated field and select Edit.

  2. In the Calculation Editor that opens, you can do the following:

    • Edit the name of the calculated field.

    • Update the formula.

      For this example, the formula is changed to return a discount ratio for orders over 2000 USD in sales:

  3. Click OK.

    The view updates to reflect the changes automatically. You do not need to re-add the updated calculated field to the view.

See Also

Understanding Calculations in Tableau(Link opens in a new window)

Formatting Calculations in Tableau(Link opens in a new window)

Functions in Tableau(Link opens in a new window)

Create Level of Detail Expressions in Tableau(Link opens in a new window)

Transform Values with Table Calculations(Link opens in a new window)

Sours: https://help.tableau.com/current/pro/desktop/en-gb/calculations_calculatedfields_create.htm
  1. Amc naperville
  2. 2000 toyota celica p0171
  3. Palmer alaska cabins
  4. 70s music cds

Tableau Functions (Alphabetical)

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.

ABCDEFGHIJKLMNOPQRSTUVWXYZ


ABS(number)

Returns the absolute value of the given number.

Examples

The second example returns the absolute value for all the numbers contained in the field.

ACOS(number)

Returns the arc cosine of the given number. The result is in radians.

Example

ASCII(string)

Return the ASCII code for the first character of .

Example

ASIN(number)

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

Example

ATAN(number)

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

Example

ATAN2(y number, x number)

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

Example

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.

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 , compares it to a sequence of values, , , etc., and returns a result. When a value that matches 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.

Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise.

Many times you can use a group to get the same results as a complicated case function.

Examples

CEILING(number)

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

Example
Availability by data source
Data SourceSupport
Microsoft AccessNot supported
Microsoft ExcelSupported
Text FileSupported
Statistical FileSupported
Tableau ServerSupported
Actian VectorwiseNot supported
Amazon AuroraNot supported
Amazon EMR Hadoop HiveSupported
Amazon RedshiftNot supported
Aster DatabaseNot supported
Cloudera HadoopSupported
DataStax EnterpriseSupported
EXASOLNot supported
FirebirdNot supported
Google AnalyticsSupported
Google BigQuerySupported
Google Cloud SQLNot supported
Hortonworks Hadoop HiveSupported
IBM BigInsightsNot supported
IBM DB2Not supported
IBM PDA (Netezza)Not supported
MapR Hadoop HiveSupported
MarkLogicNot supported
Microsoft Analysis ServicesNot supported
Microsoft PowerPivotNot supported
Microsoft SQL ServerNot supported
MySQLNot supported
OracleNot supported
Oracle EssbaseNot supported
Actian Matrix (ParAccel)Not supported
Pivotal GreenplumNot supported
PostgreSQLNot supported
Progress OpenEdgeNot supported
SalesforceSupported
SAP HANANot supported
SAP Sybase ASENot supported
SAP Sybase IQNot supported
Spark SQLSupported
SplunkNot supported
TeradataNot supported
Teradata OLAP ConnectorNot supported
VerticaNot supported

CHAR(number)

Returns the character encoded by the ASCII code .

Example

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.

Example

CONTAINS(string, substring)

Returns true if the given string contains the specified substring.

Example

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.

CORR is available with the following data sources:

  • 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.

Note: The square of a CORR result is equivalent to the R-Squared value for a linear trend line model. See Trend Line Model Terms.

Example

You 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:

With a level of detail expression, the correlation is run over all rows. If you used a formula like (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.

Example

COT(number)

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

Example

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.

COVAR is available with the following data sources:

  • 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.

Example

The following formula returns the sample covariance of Sales and 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.

COVARP is available with the following data sources:

  • 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.

Example

The following formula returns the population covariance of Sales and Profit.

DATE(expression)

Returns a date given a number, string, or date expression.

Examples

Quotation marks are required in the second and third examples.

DATEADD(date_part, interval, date)

Returns the specified date with the specified number added to the specified of that date.

Example

This expression adds three months to the date .

DATEDIFF(date_part, date1, date2, [start_of_week])

Returns the difference between and expressed in units of .

The 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.

Example

The first expression returns 1 because when is 'monday', then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when 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 of as a string. The 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 is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Examples

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 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.

Examples

DATEPART(date_part, date, [start_of_week])

Returns of as an integer.

The 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 is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Note: When the is weekday, the parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets.

Examples

DATETIME(expression)

Returns a datetime given a number, string, or date expression.

Example

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 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 is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Examples

DAY(date)

Returns the day of the given date as an integer.

Example

DEGREES(number)

Converts a given number in radians to degrees.

Example

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.

Examples

DIV(integer1, integer2)

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

Example

DOMAIN(string_url)

Note: Supported only when connected to Google BigQuery

Given a URL string, returns the domain as a string.

Example

DOMAIN('http://www.google.com:80/index.html') = 'google.com'

ENDSWITH(string, substring)

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

Example

EXP(number)

Returns e raised to the power of the given number.

Examples

FIND(string, substring, [start])

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

Examples

FINDNTH(string, substring, occurrence)

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

Note: FINDNTH is not available for all data sources.

Example

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.

Example

When the current row index is 3, .

FLOAT(expression)

Casts its argument as a floating point number.

Examples

=

converts every value in the field to a floating point number.

FLOOR(number)

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

Example
Availability by data source
Data SourceSupport
Microsoft AccessNot supported
Microsoft ExcelSupported
Text FileSupported
Statistical FileSupported
Tableau ServerSupported
Actian VectorNot supported
Amazon AuroraNot supported
Amazon EMR Hadoop HiveSupported
Amazon RedshiftNot supported
Aster DatabaseNot supported
Cloudera HadoopSupported
DataStax EnterpriseSupported
EXASOLNot supported
FirebirdNot supported
Google AnalyticsSupported
Google BigQuerySupported
Google Cloud SQLNot supported
Hortonworks Hadoop HiveSupported
IBM BigInsightsNot supported
IBM DB2Not supported
IBM NetezzaNot supported
MapR Hadoop HiveSupported
MarkLogicNot supported
Microsoft Analysis ServicesNot supported
Microsoft PowerPivotNot supported
Microsoft SQL ServerNot supported
MySQLNot supported
OracleNot supported
Oracle EssbaseNot supported
ParAccelNot supported
Pivotal GreenplumNot supported
PostgreSQLNot supported
Progress OpenEdgeNot supported
SalesforceSupported
SAP HANANot supported
SAP Sybase ASENot supported
SAP Sybase IQNot supported
Spark SQLSupported
SplunkNot supported
TeradataNot supported
Teradata OLAP ConnectorNot supported
VerticaNot supported

FULLNAME( )

Returns the full name for the current user. This is the Tableau Server or Tableau Online full name when the user is signed in; otherwise the local or network full name for the Tableau Desktop user.

Example

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.

Example

GROUP_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.

Example

HEXBINY(number, number)

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

Example

HOST(string_url)

Note: Supported only when connected to Google BigQuery

Given a URL string, returns the host name as a string.

Example

HOST('http://www.google.com:80/index.html') = 'www.google.com:80'

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.

Examples

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 ELSEIFvalues 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.

  1. Create a new calculated field by choosing Analysis > Create Calculated Field.

  2. 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+' END

    Confirm that the status message indicates that the formula is valid, and then click OK.

  3. From the Measures area of the Data pane, drag Number of Records to Rows.

  4. 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.

  5. 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, , 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 evaluates to TRUE, then IIF returns the value. If evaluates to FALSE, then IIF returns the 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.

Examples

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.

Example

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.

Example

For the third row in the partition, .

INT(expression)

Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.

Examples

=

=

=

=

When a string is converted to an integer it is first converted to a float and then rounded.

ISDATE(string)

The ISDATE function returns if the string argument can be converted to a date and if it cannot.

Examples

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 Online full name when the user is signed in; otherwise it uses the local or network full name for the Tableau Desktop user.

Example

This example returns true if Dave Hallsten is the current user, otherwise it returns false.

ISNULL(expression)

The ISNULL function returns if the expression is and if it is not.

Example

The following example uses ISNULL in combination with IIF to replace null values with 0's.

ISMEMBEROF(string)

Returns true if the person currently using Tableau is a member of a group that matches the given string. If the person currently using Tableau is signed in, the group membership is determined by groups on Tableau Server or Tableau Online. If the person is not signed in, this function returns false.

Example

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 Online username when the user is signed in; otherwise it uses the local or network username for the Tableau Desktop user.

Example

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.

Example

When the current row index is 3 of 7, .

LEFT(string, number)

Returns the left-most number of characters in the string.

Example

LEN(string)

Returns the length of the string.

Example

LN(number)

Returns the natural logarithm of a number. Returns 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.

Example

LOG2(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 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 is computed within the Date partition, each row shows the sales value from 2 quarters into the future.

Example

computes the SUM(Profit) in the third row of the partition.

LOWER(string)

Returns , with all characters lowercase.

Example

LTRIM(string)

Returns the string with any leading spaces removed.

Example

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.

Example

LTRIM_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.

Example

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).

Examples

MAKELINE(geometry1,geometry2)

MAKELINE(Geometry1,Geometry2)

Generates a line mark between two spatial points; useful for building origin-destination maps.

Example

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.

Example

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.

Example

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.

Example

MAX(a, b)

Returns the maximum of and (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, finds the value that is highest in the sort sequence defined by the database for that column. It returns if either argument is .

Example

MAX(expression) or MAX(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the maximum of and ( and must be of the same type). Returns if either argument is .

Examples

MAX(number, number)

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

Examples

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. 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 other 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 . The first character in the string is position 1. If the optional argument is added, the returned string includes only that number of characters.

Examples

MIN(a, b)

Returns the minimum of and (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, finds the value that is lowest in the sort sequence. It returns if either argument is .

Example

MIN(expression) or MIN(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the minimum of and ( and must be of the same type). Returns if either argument is .

Examples

MIN(number, number)

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

Examples

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).

Example

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.

Example

MONTH(date)

Returns the month of the given date as an integer.

Example

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.

Example

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'.

Example

PARSE_URL('http://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.

Example

PARSE_URL_QUERY('http://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.

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.

Examples

You can also use the ^ symbol:

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.

Example

computes the running product of SUM(Profit).

RADIANS(number)

Converts the given number from degrees to radians.

Example

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 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.

Example

The 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. Use the optional 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 (3, 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.

RANK_MODIFIED(expression, ['asc' | 'desc'])

Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional 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, 3, 3, 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.

RANK_PERCENTILE(expression, ['asc' | 'desc'])

Returns the percentile rank for the current row in the partition. Use the optional argument to specify ascending or descending order. The default is ascending.

With this function, the set of values (6, 9, 9, 14) would be ranked (0.25, 0.75, 0.75, 1.00).

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.

RANK_UNIQUE(expression, ['asc' | 'desc'])

Returns the unique rank for the current row in the partition. Identical values are assigned different ranks. Use the optional 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, 3, 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.

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_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_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_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_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

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_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_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_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_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


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.

Example

REGEXP_REPLACE('abc 123', '\s', '-') = 'abc-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.

Example

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

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.

Example

REGEXP_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.

Example

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

REPLACE(string, substring, replacement)

Searches for and replaces it with . If is not found, the string is not changed.

Example

RIGHT(string, number)

Returns the right-most number of characters in .

Example

ROUND(number, [decimals])

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

Example

This example rounds every value to an integer:

Some databases, such as SQL Server, allow specification of a negative , where -1 rounds 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 with any trailing spaces removed.

Example

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.

Example

RTRIM_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 is computed within the Date partition, the result is a running average of the sales values for each quarter.

Example

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.

Example

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.

Example

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.

Example

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.

Example

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).

Examples

In this R example, .arg1 is equal to 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.

A command for Python would take this form:

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).

Examples

In this R example, .arg1 is equal to SUM([Profit]):

In the next example, k-means clustering is used to create three clusters:

A command for Python would take this form:

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).

Examples

In this R example, .arg1 is equal to SUM([Profit]):

The next example converts temperature values from Celsius to Fahrenheit.

A command for Python would take this form:

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).

Examples

In this R example, .arg1 is equal to SUM([Profit]):

The next example extracts a state abbreviation from a more complicated string (in the original form 13XSL_CA, A13_WA):

A command for Python would take this form:

SIGN(number)

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

Example

If the average of the profit field is negative, then

SIN(number)

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

Example

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.

Example

when the current partition contains five rows.

SPACE(number)

Returns a string that is composed of the specified of repeated spaces.

Example

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 , 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.

Examples

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 SourceLeft/Right ConstraintsMaximum Number of SplitsVersion Limitations
Tableau Data ExtractBothInfinite
Microsoft ExcelBothInfinite
Text fileBothInfinite
SalesforceBothInfinite
ODataBothInfinite
Google AnalyticsBothInfinite
Tableau Data ServerBothInfiniteSupported in version 9.0.
VerticaLeft only10
OracleLeft only10
MySQLBoth10
PostgreSQLLeft only prior to version 9.0; both for version 9.0 and above10
TeradataLeft only10Version 14 and later
Amazon RedshiftLeft only10
Aster DatabaseLeft only

10

Google BigQueryLeft only10
Hortonworks Hadoop HiveLeft only10
Cloudera HadoopLeft only10Impala supported starting in version 2.3.0.
Microsoft SQL ServerBoth102008 and later

SQRT(number)

Returns the square root of a number.

Example

SQUARE(number)

Returns the square of a number.

Example

STARTSWITH(string, substring)

Returns true if starts with . Leading white spaces are ignored.

Example

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.

Example

takes all of the values in the measure called 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..

Example

TIMESTAMP_TO_USEC(expression)

Note: Supported only when connected to Google BigQuery

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.

Example

TIMESTAMP_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.

Example

TLD('http://www.google.com:80/index.html') = '.com'

TLD('http://www.google.co.uk:80/index.html') = '.co.uk'

TODAY( )

Returns the current date.

Example

TOTAL(expression)

Returns the total for the given expression in a table calculation partition.

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,

UPPER(string)

Returns string, with all characters uppercase.

Example

USEC_TO_TIMESTAMP(expression)

Note: Supported only when connected to Google BigQuery

Converts a UNIX timestamp in microsseconds to a TIMESTAMP data type.

Example

USEC_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

USERNAME( )

Returns the username for the current user. This is the Tableau Server or Tableau Online username when the user is signed in; otherwise it is the local or network username for the Tableau Desktop user.

Example

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.

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.

Example

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.

Example

The following formula returns the Pearson correlation of SUM(Profit) and SUM(Sales) from the five previous rows to the current row.

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.

Example

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.

Example

The following formula returns the sample covariance of SUM(Profit) and SUM(Sales) from the two previous rows to the current row.

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).

Example

The following formula returns the population covariance of SUM(Profit) and SUM(Sales) from the two previous rows 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.

Example

computes the median of SUM(Profit) from the second row to the current row.

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.

Example

computes the maximum 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.

Example

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.

Example

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.

Example

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.

Example

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.

Example

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.

Example

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.

Example

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.

Example

XPATH_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.

Example

XPATH_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.

Example

XPATH_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.

Example

XPATH_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.

Example

XPATH_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.

Example

XPATH_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.

Example

XPATH_STRING('<sites ><url domain="org">http://www.w3.org</url> <url domain="com">http://www.tableau.com</url></sites>', 'sites/url[@domain="com"]') = 'http://www.tableau.com'


YEAR (date)

Returns the year of the given date as an integer.

Example

ZN(expression)

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

Example

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)

Sours: https://help.tableau.com/current/pro/desktop/en-us/functions_all_alphabetical.htm
Tableau Tutorial 42 - How to Create Calculated Fields in Tableau

Create a Simple Calculated Field

Sometimes your data source does not contain a field (or column) that you need for your analysis. For example, your data source might contain fields with values for Sales and Profit, but not for Profit Ratio. If this is the case, you can create a calculated field for Profit Ratio using data from the Sales and Profit fields.

This topic demonstrates how to create a simple calculated field using an example.

Step 1: Create the calculated field

  1. In a worksheet in Tableau, select Analysis > Create Calculated Field.

  2. In the Calculation Editor that opens, give the calculated field a name.

    In this example, the calculated field is called Profit Ratio.

Step 2: Enter a formula

  1. In the Calculation Editor, enter a formula.

    This example uses the following formula:

    Formulas use a combination of functions, fields, and operators. To learn more about creating formulas in Tableau, see Formatting Calculations in Tableau(Link opens in a new window) and Functions in Tableau(Link opens in a new window).

  2. When finished, click OK.

    The new calculated field is added to the Data pane. If the new field computes quantitative data, it is added to Measures. If it computes qualitative data, it is added to Dimensions.

    You are now ready to use the calculated field in the view.

Check your work! Watch how to create a simple calculated field in action:

See Also

Get Started with Calculations in Tableau(Link opens in a new window)

Formatting Calculations in Tableau(Link opens in a new window)

Functions in Tableau(Link opens in a new window)

Create Level of Detail Expressions in Tableau(Link opens in a new window)

Transform Values with Table Calculations(Link opens in a new window)

Sours: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_formulas.htm

Tableau calculated field

Understanding the Tableau Calculated Field and its Main Functions

Using calculations in Tableau is where you truly start to take your analysis to the next level. Therefore, it is imperative to understand how to use the Tableau Calculated field, or at least familiarize with the main logical functions that can be extremely useful to you.

Like any analytical software, whether that’s Excel, SAS, R, or Tableau, calculations on the Tableau calculated field are the key to going from beginner to advanced.  When you master Tableau calculations and the Tableau calculated field, you master the software tool you are using and you become an infinitely better analyst.  Understanding how these work is crucial to making Tableau work well for you. 

If you are in the field of Finance and Marketing, they would both want to see the Profit Margin applied to various analyses you’ve created.  They would also like some of the charts to provide more instant insight. This is when your knowledge in Tableau Calculated Field will become useful.

How to Create a Calculated Field in Tableau

Here are the simple steps you can follow on how to add calculated field in Tableau and when you want to create a calculation:  

  1. Right click o reate > Tableau Calculated Field, or click the drop down arrow next to Dimensions. 
  1. Name your new Tableau calculation by writing over the box that says Calculation #. 
  1. In the Tableau calculated field window below, write your calculation. 
  1. Click Apply to test the effect of your, and click Ok to apply the changes and to close out of the calculation window. 
Tableau Calculated Field

The next several sections will discuss a variety of Tableau calculation types as well as specifics on how to use Tableau’s built in calculated field functions.  While it won’t go over every Tableau function possible, it will cover the main ones.  To learn about the others, use the menu along the right hand side to see explanatory info on how the functions work as well as an example. 

Aggregate Tableau Calculations & Ratios 

Let’s say you wanted to create a ratio of something in Tableau.  Perhaps it’s Profit Margin (Profit/Sales) or Revenue per Day (Revenue/# Days).  Let’s also say that the lowest level of granularity in your data set is at the customer level.  If you create a ratio calculation in Tableau at the lowest level of grain in your data, it will work appropriately.  If customer A has Sales of $1000, and Profit of $500, it’s a 50% profit.  However, when you aggregate at a higher level – say all customers in a particular region – this Tableau calculated filed calculation will break down.  

Why?  Because Tableau is adding the percentages for each customer in the region and then averaging those percentages.  So, if customer A has a profit margin of 50%, customer B has a margin of 60%, and customer C has a margin of -25%, Tableau will show you a profit margin of 85% (50+60-25).  That’s incorrect.  We need it to sum all the sales and all the profit and then take the ratio of that.  

Example: 

Customer Sales Profit Profit Margin 
Customer A 1000 500 50% 
Customer B 500 300 60% 
Customer C 100 -25 -25% 
Total 1600 775 775/1600= 48% – CORRECT Not (50+60+(-25))/3 =  28% 

To sum all of the profit figures as well as sum all of the sales figures and then divide by the totals, the calculation on Tableau calculated field looks like:  Sum([Profit])/Sum([Sales]).  Tableau now knows to sum the figures first and then calculate the ratio, rather than sum all the individual ratios. 

Calculated Fields Tableau

To create ratios in Tableau, ensure you use the level of aggregation you need in your calculation.  Typically, this will be a sum, but can also be an average, or min/max values. 

Learn more about Tableau aggregation here.

Logical Calculations in Tableau Calculated Field

If/Then 

An If/Then statement in the Tableau Calculated field allows you to check whether certain conditions are true. This formula in the Tableau Calculated Field also gives you the ability to create groups or pull out certain fields from a dimension. They are extremely useful, but using too many with too large of a data set can cause your workbook to slow down.  

IF condition1 THEN x ELSE IF condition2 THEN y ELSE z END  You can add as many ELSE/IF conditions as you want. Here’s a specific example:  If [Ship Time] > 3:00pm THEN “Late Afternoon or Evening”  ELSEIF [Ship Time] > 12:00pm THEN “Mid Afternoon”  ELSE Morning  END 

In this case, the first condition will pick up anything that occurs after 3:00pm.  The next condition will pick up anything between noon and 3.  And the last condition will pick up everything else. 

IIF 

IIF is a Tableau Calculated Field formula that is very similar in terms of what it does as the IF statement above, but it’s syntax is more similar to an IF statement in Excel. You can use either statement. 

IIF(test, then, else, [unknown])  IIF([Ship Date] - [Order Date] > 5 (days), “Severe Problem”, “OK”) 
Calculated Field Tableau

Case/When 

Case functions are only applicable to string Tableau Calculated fields (i.e. text).  They check whether certain conditions are met within the dimension or parameter and return a value specified by you. 

CASE [Month]  WHEN “January” THEN “Jan”  WHEN “February” THEN “Feb”  WHEN “March” THEN “Mar”  Etc…  END 

These can also be used with parameters.  The statement below enables you to select which dimension you want to show in a particular chart.  The setup of the chart remains the same but the information shown can be changed. 

CASE [Choose Dimension Parameter]  WHEN “Marketing Channel” THEN [Marketing Channel]  WHEN “Customer Segment” THEN [Customer Segment]  WHEN “Product Category” THEN [Product Category]  END 

IfNull 

This Tableau Calculated Field function checks whether a field is null, or has no data in it.  The first Tableau field will be the field you want to use if it isn’t null.  If the Tableau field is null, that’s the second condition.  But, there’s one caveat: the data types have to match between the first and second condition.  If the first Tableau field is a date, the second field must be a date.  If the first Tableau field is text, the second must be text, and so on. 

IfNull([Order ID,0) 

  • This checks whether the product has a ship date.  If it does, it will return the ship date; if it doesn’t have a date, it will return “Hasn’t Shipped Yet.” 

IsNull 

This Tableau calculation function is simply a true/false statement that checks whether a record is null. 

IsNull([Zipcode]) 

  • This checks whether the zip code is null or not.  If you put Number of Records next to it, you can see how many are null vs. how many are populated.  If you use it on the Filter shelf, you can filter out nulls or show only those with nulls.  

You can also combine this with the IF statement for a more advanced filter.   

If  IsNull([Ship Date]) THEN “Hasn’t Shipped Yet” End 

ZN 

The ZN function checks whether measures are null and converts the null values to a zero.  So, imagine the following data: 

Customer ID Amount Paid 
0123 $500 
4567 $2500 
8998      
0009 $3000 

The blank Tableau Calculated field is a null value and will be converted to a zero. 

ZN’s must be aggregated. 

ZN(sum([Amount Paid]) 

Practice Using the Tableau Calculated Field

Since both Finance and Marketing would need to see the Profit Margin applied to various analyses you create and charts to provide more instant insight,  We’ll use Tableau filed calculations to get those. 

  1. Create a Tableau calculated field with the following formula SUM([Profit])/SUM([Sales]). 
  1. Name it Profit Margin. 
  1. Go to the Profit by Marketing Channel worksheet. 
  1. If you don’t have it, bring Marketing Channel to Rows and Profit to Columns. 
  1. Add Profit Margin onto the Tableau calculated field Columns shelf. 
  1. Turn on labels using the Labels shelf. 
  1. Right click on the labels on the profit margin side of the chart and choose Format. 
  1. In the formatting pane at the left, ensure that the Fields drop down menu is set to Agg(Profit Margin). 
  1. Change both the axis and the chart labels to be percentages, 0 decimal points. 
  1. Bring out another instance of Profit in between Profit and Profit Margin on the Columns shelf. 
  1. On this pill you just dragged out, click the drop down arrow and create a Quick Table Calc for Percent of Total.  You’ll now have three charts side by side. 
  1. Sort the Profit Margin in descending order.  Determine some areas where you could have some quick wins, like Social Media. 
  1. Rename the Tableau calculated filed tab “Profit Margin by Marketing Channel.” 
  1. Create a new Tableau calculated field.  This will combine both a date calculation and a case statement.  Type in the following calculation: 
CASE Datename('month',[Order Date])  WHEN 'January' THEN 'Winter'  WHEN 'February' THEN 'Winter'  WHEN 'March' THEN 'Spring'  WHEN 'April' THEN 'Spring'  WHEN 'May' THEN 'Spring'  WHEN 'June' THEN 'Summer'  WHEN 'July' THEN 'Summer'  WHEN 'August' THEN 'Summer'  WHEN 'September' THEN 'Fall'  WHEN 'October' THEN 'Fall'  WHEN 'November' THEN 'Fall'  WHEN 'December' THEN 'Winter'  END 
  1. Name the calc “Seasons.”  This is saying that if the text version of the month equals the following names then classify them according to their appropriate season. 
  1. Now find the Order Qty by Day chart we created.  It probably hasn’t yet been used in a dashboard so you’ll need to go to the worksheet directly. 
    • If you don’t have it, bring Order Quantity to Rows.  
    • Bring Order Date to Columns
    • Click the drop down arrow on Order Date and set it to be a continuous Month (May 2015) from the second section of dates in the menu. 
  1. Now, change the Order Date pill on Tableau Calculated field from a continuous (green) day to a discrete (blue) Week Number, accessed via the sideways arrow in the first date section of the drop down, where it says “More.” 
  1. Drag your Seasons calc onto Color. 
    • If you can’t find where your Seasons calc went, use the magnifying glasss up next to Dimensions. 

We hope you learned a lot about creating Tableau Calculated Field and the most useful functions you can use in your data analysis. We will also discuss the different Tableau Date Functions. To learn Tableau faster, be sure to check out our Tableau Classes for individuals and corporations.

Sours: https://datacrunchcorp.com/tableau-calculated-field/
How to Master Calculations in Tableau

How to Create Calculated Fields in Tableau (Tableau Calculations)

Tableau calculations help you create new data based on the existing one. A very good example of calculated field is Profit ratio – which is as a result of dividing SUM(Profit) and SUM(Sales).

You can use calculated fields in different ways, such as;

  • Segmenting data

  • Converting data type of a field – such as string to date field

  • Aggregating data

  • Filtering results

  • Calculating ratios etc.

You can build your first calculated field in Tableau as follows;

Step 1: Open the calculation editor.

You can open the calculation editor in the following three ways;

1. By going to the Analysis menu >> Create Calculated Field…

2. Right click anywhere on the dimension or measure area >> Create >> Calculated Field…

3. On the data pane – open the drop down menu on top of the dimension area >> Create Calculated Field…

Step 2: Name your calculation and enter your formula in the calculation editor

In this case, have named the calculation – Profit ratio

And used this formula - SUM(Profit)/SUM(Sales)

Click okay.

Note: The new calculated field is added to the data pane either under dimensions or measures. If your calculation computes quantitative data then it’s added to the measures. If it computes qualitative data then it’s added to the dimensions.

In this case, Profit ratio is added to the measure area.

Note: The complexity of your calculation will vary depending with the type of calculation (basic, level of detail (LOD) expressions or Table calculations) and kind of challenge you’re working on.

To receive more of the Tableau tips and tricks – kindly subscribe to our mailing list below.

Thank you for reading.

Sours: https://www.rigordatasolutions.com/post/how-to-create-calculated-fields-in-tableau-tableau-calculations

Now discussing:

.



845 846 847 848 849