TAB106 / Calculated Fields

Calculated Fields are additional fields that are a combination of formulas and existing fields in your data source. When you create a calculated field, you are essentially creating a new data field that can be used in your data visualisations.

There are three main types of Calculated Fields:

• 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 visualization level of detail (an aggregate calculation).
• Table Calculations – Table calculations allow you to transform values at the level of detail of the visualization only. We will go through Table Calculations in-depth in TAB113.
• Level of Detail (LOD) expressions – Just like basic calculations, LOD calculations allow you to compute values at the data source level and the visualization level. However, LOD calculations give you even more control over the level of granularity you want to compute. We will go through LOD in-depth in TAB114.

For the remainder of this article, we will go through Basic Calculations.

Note: We can only introduce you to Basic Calculations, but you will need to practice and get used to the common Calculations as well as build an awareness of what Calculations are at your disposal.

Calculated Fields

Let us get started straight away by creating a Calculated Field, you actially have a few options on how to do this:

1. In a worksheet and Menu Bar, go to Analysis and select Create Calculated Field.
2. Right-click in an empty space in the Data Pane and select Create Calculated Fields.
3. Right-click on a Field in the Data Pane, go to Create and select Calculated Field.
4. In the Data Pane, on the right of the Dimension header, click on the Down Arrow (Down Carrot), and select Create Calculated Field.
5. You can type formulas directly in the Columns, Rows and Marks and then drag the result onto the Data Pane..

As you can see there are several different ways of creating a new Calculated Field depending on what is quickest at this moment. Please have a try and create Calculated Fields using all 5 methods.

Note: If you know of an additional way of creating Calculated Fields, please do let me know. I am sure there are other ways too.

Calculation Editor

Once you go through one of hte above ptions you will be presented with teh following:

Let us have a look at what we have:

1. Is where you put the name of your Calculated Field, this must be unique.
2. This is where you will write your Calculated Field formula. This includes auto-complete as well as auto-formular-validation.
3. This is a logical grouping of available Calculations. We will go through the formulas based on these groupings. You can also search to see if your formula you require exists.
4. Here is the list of fomulas that you have available to you at present.
5. If you click on a function listed in 4, you will see a short description.

All of this is available to you to use in your work. So let us go through what is available to us.

Note: There is current no real way of defining corporate custom functions, this is a pet peeve, but I hope this will be a feature in the future.

LITERALS, OPERATORS AND SYNTAX

Your Calculations will be made up of a Formula that returns a value; your formula must be valid and can comprise of Data Fields, Calculated Fields, Operators and Functions. We are going to look through the list of functions available, but first:

LITERALS

• Comments – are defined by two forward slashes: //
• Fields – are defined by the field name within square brackets: [Data Field Name]
• Literals
  • String literals are text between single or double quotes: “Text”, ‘Text’.
  • Date literals are date text between #1 January 2019#.
  • Boolean literals are written as TRUE or FALSE. This is not case sensitive.
  • Numeric literals are written as numbers which can contain decimals or who numbers.
  • Null literals are written as NULL. This is not case sensitive.
• Parameters – are defined in the same way as Fields.

OPERATORS

• + is the Addition operator.
• – is the Subtraction operator.
• * is the Multiplication operator.
• / is the Division operator.
• % is the Modulo operator.
• ^ is the Power operator.
• = or == is the Equality operator.
• != or <> is the Not Equal To operator.
• > is the Greater Than operator.
• >= is the Greater Than or Equal to operator.
• < is the Less Than operator.
• <= is the Less Than or Equal to operator.

OPERATOR PRECEDENCE

Ideally you should always use parenthesis to make your formulars clear, but here is the Tableau Operator Precedence.

NUMBER FUNCTIONS

• ABS() – Returns the absolute value of the given number.
• ACOS() – Returns the arc cosine of the given number. The result is in radians.
• ASIN() – Returns the arc sine of a given number. The result is in radians.
• ATAN() – Returns the arc tangent of a given number. The result is in radians.
• ATAN2() – Returns the arc tangent of two given numbers (x and y). The result is in radians.
• CEILING() – Rounds a number to the nearest integer of equal or greater value.
• COS() – Returns the cosine of an angle. Specify the angle in radians.
• COT() – Returns the cotangent of an angle. Specify the angle in radians.
• DEGREES() – Converts a given number in radians to degrees.
• DIV() – Returns the integer part of a division operation, in which integer1 is divided by integer2.
• EXP() – Returns e raised to the power of the given number.
• FLOOR() – Rounds a number to the nearest integer of equal or lesser value.
• HEXBINX() – 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.
• HEXBINY() – 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.
• LN() – Returns the natural logarithm of a number. Returns Null if number is less than or equal to 0.
• LOG() – Returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used.
• MAX() – 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.
• MIN() – 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.
• PI() – Returns the numeric constant pi: 3.14159.
• POWER() – Raises the number to the specified power.
• RADIANS() – Converts the given number from degrees to radians.
• ROUND() – Rounds numbers to a specified number of digits. The decimalsargument specifies how many decimal points of precision to include in the final result. If decimals is omitted, number is rounded to the nearest integer.
• SIGN() – 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.
• SIN() – Returns the sine of an angle. Specify the angle in radians.
• SQRT() – Returns the square root of a number.
• SQUARE() – Returns the square of a number.
• TAN() – Returns the tangent of an angle. Specify the angle in radians.
• ZN() – Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.

Take a moment and experiment with the various number functions. I personally use SIN(), COS() and RADIANS() frequently for drawing. ZN() is also fantastic for dealing with null values. MIN() and MAX() and others on the list are commonplace in Tableau.

STRING FUNCTIONS

• ASCII – Returns the ASCII code for the first character of string.
• CHAR() – Returns the character encoded by the ASCII code number.
• CONTAINS() – Returns true if the given string contains the specified substring.
• ENDSWITH() – Returns true if the given string ends with the specified substring. Trailing white spaces are ignored.
• FIND() – 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.
• FINDNTH() – Returns the position of the nth occurrence of substring within the specified string, where n is defined by the occurrence argument.
• LEFT() – Returns the left-most number of characters in the string.
• LEN() – Returns the length of the string.
• LOWER() – Returns string, with all characters lowercase.
• LTRIM() – Returns the string with any leading spaces removed.
• MAX() – 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.
• MID() – 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.
• MIN() – 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.
• REPLACE() – Searches string for substring and replaces it with replacement. If substring is not found, the string is not changed.
• RIGHT() – Returns the right-most number of characters in string.
• RTRIM() – Returns string with any trailing spaces removed.
• SPACE() – Returns a string that is composed of the specified number of repeated spaces.
• SPLIT() – Returns a substring from a string, using a delimiter character to divide the string into a sequence of tokens.
• STARTSWITH() – Returns true if string starts with substring. Leading white spaces are ignored.
• TRIM() – Returns the string with leading and trailing spaces removed.
• UPPER() – Returns string, with all characters uppercase.

Have a look at these functions and try them out. I personally use SPLIT(), LEN(), TRIM() and surprisingly CHAR() very often. Explore and Experiment.

DATE FUNCTIONS

Before we go into dates, let us first look at a table of date parts.

Now let us look at the functions available to us.

• DATEADD() – Returns the specified date with the specified number interval added to the specified date_part of that date.
• DATEDIFF() – Returns the difference between date1 and date2 expressed in units of date_part.
• DATENAME() – 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.
• DATEPART() – Returns date_part of date as an integer.
• DATETRUNC() – 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.
• DAY() – Returns the day of the given date as an integer.
• ISDATE() – Returns true if a given string is a valid date.
• MAKEDATE() – Returns a date value constructed from the specified year, month, and date.
• MAKEDATETIME() – 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.
• MAKETIME() – Returns a date value constructed from the specified hour, minute, and second.
• MAX() – 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.
• MIN() – 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.
• MONTH() – Returns the month of the given date as an integer.
• NOW() – Returns the current date and time.
• TODAY() – Returns the current date.
• YEAR() – Returns the year of the given date as an integer.

I do not think there is a single function I do not use with regularity. One of the key things and reference points is the date parts in the section above.

CONVERSTION FUNCTIONS

• DATE() – Returns a date given a number, string, or date expression.
• DATETIME() – Returns a datetime given a number, string, or date expression.
• DATEPARSE() – Converts a string to a datetime in the specified format. Support for some locale-specific formats is determined by the computer’s system settings.
• FLOAT() – Casts its argument as a floating point number.
• INT() – Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.
• STR() – Casts its argument as a string.

You really need to know all of the above and changing data types in formulas is a must.

LOGICAL FUNCTIONS

• AND – Performs a logical conjunction on two expressions.
• CASE, WHEN, THEN, ELSE, END – Performs logical tests and returns appropriate values. 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.
• IF, THEN, ELSEIF, ELSE, END – Tests a series of expressions returning the <then> value for the first true <expr>.
• IFNULL() – Returns <expr1> if it is not null, otherwise returns <expr2>.
• IIF() – Checks whether a condition is met, and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown.
• ISDATE() – Returns true if a given string is a valid date.
• ISNULL() – Returns true if the expression does not contain valid data (Null).
• NOT – Performs logical negation on an expression.
• OR – Performs a logical disjunction on two expressions.
• ZN() – Returns <expression> if it is not null, otherwise returns zero.

This is also essential viewing and something that you must have a play with and understand.

AGGREGATE FUNCTIONS

• ATTR() – Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored.
• AVG() – Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.
• COLLECT() – An aggregate calculation that combines the values in the argument field. Null values are ignored.
• CORR() – Returns the Pearson correlation coefficient of two expressions.
• COUNT() – Returns the number of items in a group. Null values are not counted.
• COUNTD() – Returns the number of distinct items in a group. Null values are not counted.
• COVAR() – Returns the sample covariance of two expressions.
• COVARP() – Returns the population covariance of two expressions.
• MAX() – Returns the maximum of an expression across all records. If the expression is a string value, this function returns the last value where last is defined by alphabetical order.
• MEDIAN() – Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored.
• MIN() – Returns the minimum of an expression across all records. If the expression is a string value, this function returns the first value where first is defined by alphabetical order.
• PERCENTILE() – Returns the percentile value from the given expression corresponding to the specified number.
• STDEV() – Returns the statistical standard deviation of all values in the given expression based on a sample of the population.
• STDEVP() – Returns the statistical standard deviation of all values in the given expression based on a biased population.
• SUM() – Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored.
• VAR() – Returns the statistical variance of all values in the given expression based on a sample of the population.
• VARP() – Returns the statistical variance of all values in the given expression on the entire population.

Aggregating data is key to generating concise and performant dashboards, so therefore, it is crucial that you spend time understanding what is available to you. Experiment with the functions above, and do a Google search on some functions that you are not sure about, you will find alot of information about there.

USER FUNCTIONS

• 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.
• ISFULLNAME() – 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.
• ISMEMBEROF() – 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.
• ISUSERNAME() – 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.
• 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.
• 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.

These are really useful and I use them often to provide additional auditing information in a dashboard, or for row level security or use based filtering.

This is not a full list but hopefully will highlight the vast majority of basic functions that are available to you. For a full, comprehensive and managed list, please visit the Tableau Official Documentation at https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions.htm

Summary

In this part of our introductory series, we explored the Basic Calculations and the various available options. I would suggest connecting to your data source and having a play around with the different formulas to get used to the syntax as well as what is available.

I hope you enjoyed this tutorial, and look forward to the next one. I am always looking for ways to help share knowledge around the community, so if you want to know more about something, and wish there was a tutorial out there, do get in touch on Twitter at @Tableau_Magic.

• Introduction to Tableau (TAB101).
• Installation of Tableau Desktop (TAB102).
• Data Sources and Connectivity (TAB103).
• Building Worksheets (TAB104).
  • Exploring Marks (TAB105).
  • Exploring Calculated Fields (TAB106).
  • Exploring Filters (TAB107)
  • Exploring Parameters (TAB108).
  • Exploring Sets (TAB109).
• Building Dashboards (TAB110).
  • Exploring Layouts (TAB111).
  • Exploring Actions (TAB112).
• Advanced Calculations:
  • Table Calculations (TAB113)
  • Level of Details (LOD) (TAB114)