Diving Deep with Table Calculations

Table calculations are one of the most powerful features in Tableau. They enable solutions that really couldn’t be achieved any other way (short of writing a custom application or complex custom SQL scripts!). The features include the following:

• They make it possible to use data that isn’t structured well and still get quick results without waiting for someone to fix the data at the source.
• They make it possible to compare and perform secondary aggregations and work across values within a visualization.
• They open incredible possibilities for analysis and creative approaches to solving problems, highlighting insights, or improving the user experience.

Table calculations range in complexity, from incredibly easy to create (a couple of clicks) to extremely complex (requiring an understanding of addressing, partitioning, and data densification, for example). We’ll start off simple and move toward...

An overview of table calculations

Table calculations are different from all other calculations in Tableau. Row-level, aggregate calculations, and level of detail (LOD) expressions, which we explored in the previous chapters, are performed as part of the query to the data source. If you were to examine the queries sent to the data source by Tableau, you’d find the code for your calculations translated into whatever implementation of SQL the data source used.

Table calculations, on the other hand, are performed after the initial query. Here’s a diagram that demonstrates how aggregated results are stored in Tableau’s cache:

Figure 6.1: Table calculations are computed in Tableau’s cache of aggregated data

Table calculations are performed on the aggregate table of data in Tableau’s cache right before the data visualization is rendered. As we’ll see, this is important to understand for multiple reasons, including the following:

...

Creating and editing table calculations

There are several ways to create table calculations in Tableau, including:

• Using the drop-down menu for any active field used as a numeric aggregate in the view, selecting Quick Table Calculation and then the desired calculation type
• Using the drop-down menu for any active field that is used as a numeric aggregate in the view, selecting Add Table Calculation, then selecting the calculation type and adjusting any desired settings
• Creating a calculated field and using one or more table calculation functions to write your own custom table calculations

The first two options create a quick table calculation, which can be edited or removed using the drop-down menu on the field and by selecting Edit Table Calculation... or Clear Table Calculation. The third option creates a calculated field, which can be edited or deleted like any other calculated field.

A field on a shelf in the view that is using a table calculation...

Quick table calculations

Quick table calculations are predefined table calculations that can be applied to fields used as measures in the view. These calculations include common and useful calculations such as Running Total, Difference, Percent Difference, Percent of Total, Rank, Percentile, Moving Average, YTD Total (year-to-date total), Compound Growth Rate, Year over Year Growth, and YTD Growth. You’ll find applicable options on the drop-down list on a field used as a measure in the view, as shown in the following screenshot:

Figure 6.4: Using the dropdown, you can create a quick table calculation from an aggregate field in the view

Consider the following example using the sample Superstore Sales data:

Figure 6.5: The first SUM(Sales) field is a normal aggregate. The second has a quick table calculation of Running Total applied

Here, Sales over time is shown. Sales has been placed on the Rows shelf twice and the second SUM(Sales) field has had the...

Fundamental table calculation concepts

Although it is quite easy to create quick table calculations, it is essential to understand some fundamental concepts. We’ll take a look at these next, starting with the difference between relative and fixed table calculations.

Relative versus fixed

We’ll look at the details shortly, but first, it is important to understand that table calculations may be computed in one of the following two ways:

• Relative: The table calculation will be computed relative to the layout of the table. They might move across or down the table. Rearranging dimensions in a way that changes the table will change the table calculation results. As we’ll see, the key for relative table calculations is scope and direction. When you set a table calculation to use a relative computation, it will continue to use the same relative scope and direction, even if you rearrange the view.

  The term relative here is different from...

Custom table calculations

Before we move on to some practical examples, let’s briefly discuss how to write your own table calculations, instead of using quick table calculations. You can see a list of available table calculation functions by creating a new calculation and selecting Table Calculation from the drop-down list under Functions.

For each of the examples, we’ll set Compute Using | Category. This means that Department will be the partition.

You can think of table calculations broken down into several categories. The following table calculations can be combined and even nested just like other functions.

Meta table functions

These are the functions that give you information about partitioning and addressing. These functions also include Index, First, Last, and Size:

• Index gives an increment as it moves along the addressing within the partition
• First gives the offset from the first row in the partition, so the first row in each...

Practical examples

Having looked at some of the essential concepts of table calculations, let’s consider some practical examples. We’ll look at several examples, although the practical use of table calculations is nearly endless. You can do everything from running sums and analyzing year-over-year growth to viewing percentage differences between categories, and much more.

Year over year growth

Often, you may want to compare year over year values. How much has our customer base grown over the last year? How did sales in each quarter compare to sales in the same quarter last year? These types of questions can be answered using Year over Year Growth.

Tableau exposes Year over Year Growth as one option in the quick table calculations. Here, for example, is a view that demonstrates Sales by Quarter, along with the percentage difference in sales for a quarter compared with the previous year:

Figure 6.25: Year over year growth of Sales

The second Sum...

Summary

We’ve covered a lot of concepts surrounding table calculations in this chapter. You now have a foundation for using the simplicity of quick table calculations and leveraging the power of advanced table calculations. We’ve looked at the concepts of scope and direction as they apply to table calculations that operate relative to the row and column layout of the view. We’ve also considered the related concepts of addressing and partitioning as they relate to table calculations that have computations fixed to certain dimensions.

The practical examples we’ve covered barely scratch the surface of what is possible, but should give you an idea of what can be achieved. The kinds of problems that can be solved and the diversity of questions that can be answered are almost limitless.

We’ll turn our attention to some lighter topics in the next couple of chapters, looking at formatting and design, but we’ll certainly see another table calculation...