Introduction to calculations

A calculation is often referred to as a Calculated Field in Tableau because, in most cases, when you create a calculation, it will show up as either a new measure or dimension in the data pane. Calculations consist of code that's made up of functions, operations, and references to other fields, parameters, constants, groups, or sets. This code returns a value. Sometimes, this result is per row of data, and sometimes it is done at an aggregate level. We'll consider the difference between Tableau's major calculation types next.

The four main types of calculations

The most fundamental way to understand calculations in Tableau is to think of four major types of calculations:

• Row-level calculations: These calculations are performed for every row of underlying data.
• Aggregate calculations: These calculations are performed at an aggregate level, which is usually defined by the dimensions used in the view.
• Level of detail calculations: These special calculations are aggregations that are performed at a specified level of detail, with the results available at the row level.
• Table calculations: These calculations are performed on the table of aggregate data that has been returned by the data source to Tableau.

Understanding and recognizing the four main types of calculations will enable you to leverage the power and potential of calculations in Tableau.

In this chapter, we'll take a close look at two of the four main types of calculations in Tableau: row-level and aggregate calculations. We'll consider the final two types in Chapter 5, Leveraging Level of Detail Calculations, and Chapter 6, Diving Deep with Table Calculations.

Now we have examined the major calculation types in Tableau, we will see how they are created and edited.

Creating and editing calculations

There are multiple ways to create a calculated field in Tableau:

1. Select Analysis | Create Calculated Field... from the menu.
2. Use the drop-down menu next to Dimensions in the Data pane:
   

   Figure 4.1: The Create Calculated Field… option

3. Right-click an empty area in the Data pane and select Create Calculated Field....
4. Use the drop-down menu on a field, set, or parameter in the data pane and select Create | Calculated Field.... The calculation will begin as a reference to the field you selected.
5. Double-click an empty area on the Rows, Columns, or Measure Values shelves, or in the empty area on the Marks card to create an ad hoc calculation (though this will not show the full calculation editor).
6. When you create a calculated field, it will be part of the data source that is currently selected at the time you create it. You can edit an existing calculated field in the data pane by using the drop-down menu and selecting Edit....

The interface for creating and editing calculations looks like this:

Figure 4.2: The creating and editing calculations interface

This window has several key features:

• Calculated field name: Enter the name of the calculated field here. Once created, the calculated field will show up as a field in the data pane with the name you entered in this text box.
• Code editor: Enter code in this text area to perform the calculation. The editor includes autocomplete for recognized fields and functions. Additionally, you may drag and drop fields and text snippets to and from the code editor and the data pane and view.
• An indicator at the bottom of the editor will alert you to errors in your code. Additionally, if the calculation is used in views or other calculated fields, you will see a drop-down indicator that will let you see the dependencies. Click the Apply button to apply changes to the calculation throughout the workbook while leaving the calculation editor open. The OK button will save the code changes and close the editor. If you wish to discard any changes you've made, click the X button in the upper-right corner to cancel the changes.
• The functions list contains all the functions that you can use in your code. Many of these functions will be used in examples or discussed in this chapter. Tableau groups various functions according to their overall use:
  • Number: Mathematical functions, such as rounding, absolute value, trig functions, square roots, and exponents.
  • String: Functions that are useful for string manipulation, such as getting a substring, finding a match within a string, replacing parts of a string, and converting a string value to uppercase or lowercase.
  • Date: Functions that are useful for working with dates, such as finding the difference between two dates, adding an interval to a date, getting the current date, and transforming strings with non-standard formats into dates.
  • Type Conversion: Functions that are useful for converting one type of field to another, such as converting strings into integers, integers into floating-point decimals, or strings into dates. We'll cover the major Tableau data types in the next section.
  • Logical: Decision-making functions, such as if then else logic or case statements.
  • Aggregate: Functions that are used for aggregating such as summing, getting the minimum or maximum values, or calculating standard deviations or variances.
  • Pass Through (only available when connected live to certain databases, such as SQL Server): These functions allow you to pass through raw SQL code to the underlying database and retrieve a returned value at either a row level or aggregate level.
  • User: Functions that are used to obtain usernames and check whether the current user is a member of a group. These functions are often used in combination with logical functions to customize the user's experience or to implement user-based security when publishing to Tableau Server or Tableau Online.
  • Table calculation: These functions are different from the others. They operate on the aggregated data after it is returned from the underlying data source and just prior to the rendering of the view.
  • Spatial: These functions allow you to perform calculations with spatial data.
• Selecting a function in the list or clicking a field, parameter, or function in the code will reveal details about the selection on the right. This is helpful when nesting other calculated fields in your code, when you want to see the code for that particular calculated field, or when you want to understand the syntax for a function.

With a good understanding of the interface, let's briefly look at some foundational concepts for calculations.

Data types

Fundamental to the concept of calculations are data types, which describe the kind of information stored by a field, parameter, or returned by a function. Tableau distinguishes six types of data:

• Number (decimal): These are numeric values that include places after the decimal. Values such as 0.02, 100.377, or 3.14159 are decimal values.
• Number (whole): These are integer or whole numbers with no fractional values or places after the decimal. Values such as 5, 157, and 1,455,982 are whole numbers.
• Date and Time: These are dates along with times. Values such as November 8, 1980 12:04:33 PM are date and time types.
• Date: These are dates without times. Values such as July 17, 1979 are date types.
• String: These are a series of characters. A string may consist of a mixture of alphabetic characters, numeric characters, symbols, or special characters. They may even be blank (empty). Values such as Hello World, password123, and %$@*! are all strings. In code, strings will be surrounded by single or double quotes.
• Boolean: This is a true or false value. The values TRUE, FALSE, and the expressions 1=1 (which evaluates as true) and 1=2 (which evaluates as false) are all Boolean types.
• Spatial: A complex value that describes a location, line or shape as it relates to a spatial area.

Every field in Tableau has one of these data types and every function in Tableau returns one of these data types. Some functions expect input that matches some of these types and you'll receive errors if you try to pass in the wrong type.

Some types can be converted to other types. For example, using some of the type conversion functions mentioned above, you could convert the string "2.81" to the decimal value 2.81. You could convert that decimal value to a whole number, but in that case, you'd lose the places after the decimal value and the whole number would simply be 2.

A data type is different from the format displayed. For example, you may choose to format a decimal as a percentage (for example, 0.2 could be shown as 20%), as currency (for example, 144.56 could be formatted as $144.56), or even as a number with 0 decimals (for example, 2.81 would be rounded to 3).

Pay attention to the data types of fields and functions as we continue.

Additional functions and operators

Tableau supports numerous functions and operators. In addition to the functions that are listed on the calculation screen, Tableau supports the following operators, keywords, and syntax conventions:

You'll see these operators and functions throughout the next few chapters, so familiarize yourself with their uses. Now, let's consider the data that will guide us through some practical examples.

Example data

Before we get started with some examples, let's consider a sample dataset that will be used for the examples in this chapter. It's simple and small, which means we will be able to easily see how the calculations are being done.

This dataset is included as Vacation Rentals.csv in the \Learning Tableau\Chapter 04 directory of this book's resources, and is also included in the Chapter 4 workbook as a data source named Vacation Rentals:

The dataset describes several vacation rental properties, the renters, the start and end dates of the rental period, the discount, rent, and tax per night. We'll use it throughout the rest of the chapter as we see some examples of calculations. Let's start with row-level calculations.