Starting an Adventure with Calculations and Parameters

We have already seen what amazing discovery, analysis, and data storytelling is possible in Tableau by simply connecting to data and dragging and dropping fields. Now, we’ll set off on an adventure with calculations.

Calculations significantly extend the possibilities for analysis, design, and interactivity in Tableau. In this chapter, we’ll see how calculations can be used in a wide variety of ways. We’ll see how calculations can be used to address common issues with data, extend data by adding new dimensions and measures, and provide additional flexibility in interactivity.

At the same time, while calculations provide incredible power and flexibility, they introduce a level of complexity and sophistication. As you work through this chapter, try to understand the key concepts behind how calculations work in Tableau. As usual, follow along with the examples, but feel free to explore and experiment...

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

Row-level calculations

We’ll walk through several examples of row-level calculations in this section. You’ll find the completed calculations in the Complete workbook, but you might prefer to start from scratch in the Starter workbook. We won’t necessarily cover creating a visualization for every example, but try building some of your own as you work through the examples.

Concatenating strings

We’ll start with a very simple example and then build up in complexity. In the Chapter 04 workbook, create a new calculated field called Full Name with the following code:

[First] + " " + [Last] 

This code concatenates the strings of First and Last with a space in between them. Your calculation editor should look something like the following:

Figure 4.3: Creating the Full Name calculation in the editor

After clicking OK, you should notice a new Full Name field in the data pane. The value for that field is calculated per row of...

Aggregate calculations

We’ve already considered aggregations such as sum, min, and max in Tableau. Often, you’ll use fields as simple aggregations in the view. But sometimes, you’ll want to use aggregations in more complex calculations.

For example, you might be curious to explore the percentage of the rent that was discounted. There is no such field in the data. It could not really be stored in the source, because the value changes based on the level of detail present in the view (for example, the discount percentage for an individual unit will be different from the discount percentage per floor or per building). Rather, it must be calculated as an aggregate and recalculated as the level of detail changes.

Let’s create a calculation named Discount % with the following code:

SUM([Discount]) / SUM([Rent]) 

This code indicates that the sum of Discount should be divided by the sum of Rent. This means that all the values of Discount will be...

Parameters

Before moving on to some additional examples of row-level and aggregate calculations, let’s take a little side trip to examine parameters, given that they can be used in incredible ways in calculations.

A parameter in Tableau is a placeholder for a single, global value such as a number, date, or string. Parameters may be shown as controls (such as sliders, drop-down lists, or type-in text boxes) to end users of dashboards or views, giving them the ability to change the current value of the parameter. Parameter values may even be changed with actions, as you’ll see in Chapter 8, Telling a Data Story with Dashboards.

The value of a parameter is global so that if the value is changed, every view and calculation in the workbook that references the parameter will use the new value. Parameters provide another way to provide rich interactivity to the end users of your dashboards and visualizations.

Parameters can be used to allow anyone interacting with...

Practical examples of calculations and parameters

Let’s turn our attention to some practical examples of row-level and aggregate calculations. The goal is to learn and understand some of what is possible with calculations. You will be able to build on these examples as you embark on your analysis and visualization journey.

A great place to find help and suggestions for calculations is the official Tableau forums at https://community.tableau.com/s/explore-forums.

Fixing data issues

Often, data is not entirely clean. That is, it has problems that need to be corrected before meaningful analysis can be accomplished. For example, dates may be incorrectly formatted, or fields may contain a mix of numeric values and character codes that need to be separated into multiple fields. Calculated fields can often be used to fix these kinds of issues.

We’ll consider using Tableau Prep—a tool designed to shape and cleanse data—in Chapter...

Performance considerations

When working with a small dataset and an efficient database, you often won’t notice inefficient calculations. With larger datasets, the efficiency of your calculations can start to make a difference to the speed at which a view is rendered.

Here are some tips for making your calculations as efficient as possible:

• Boolean and numeric calculations are much faster than string calculations. If possible, avoid string manipulation and use aliasing or formatting to provide user-friendly labels. For example, don’t write the following code: IF [value] == 1 THEN "Yes" ELSE "No" END. Instead, simply write [value] == 1, and then edit the aliases of the field and set True to Yes and False to No.
• Look for ways to increase the efficiency of a calculation. If you find yourself writing a long IF ELSEIF statement with lots of conditions, see whether there are one or two conditions that you can check first to eliminate...

Summary

Calculations open amazing possibilities in Tableau. You are no longer confined to the fields in the source data. With calculations, you can extend the data by adding new dimensions and measures, fix bad or poorly formatted data, and enhance the user experience with parameters for user input and calculations that enhance the visualizations.

The key to using calculated fields is understanding the four main types of calculations in Tableau. Row-level calculations are performed for every row of source data. These calculated fields can be used as dimensions or they can be aggregated as measures. Aggregate calculations are performed at the level of detail that’s defined by the dimensions that are present in a view. They are especially helpful, and even necessary, when you first aggregate components of the calculation before performing additional operations.

In the next chapter, we’ll explore the third of the four main types of calculations: level of detail...