Introducing Table Calculations

The topic of table calculations in Tableau is so rich and deep that it alone could legitimately be the subject of an entire book. Exploring the various options that are available for each table calculation function and the various ways that table calculations can be applied is an interesting and rewarding endeavor. As you review the examples in this chapter, you will undoubtedly encounter techniques that you can apply in your day-to-day work; however, you may struggle to understand why some of these techniques work. This chapter was written to provide ways of thinking about table calculations that will prove useful in your journey toward mastering this fascinating topic. Along the way, some practical examples will be considered as well.

Of the dozens of blogs, forum posts, conference sessions, articles, and white papers reviewed for this chapter, Jonathan Drummy’s blog post (http://drawingwithnumbers.artisart.org/at-the-level-unlocking-the...

Partition and direction of addressing

As discussed in Chapter 1, Reviewing the Basics, calculated fields can be categorized as either row-level, aggregate-level, or table-level. For row- and aggregate-level calculations, the underlying data source engine does most (if not all) of the computational work and Tableau merely visualizes the results. For table calculations, Tableau also relies on the underlying data source engine and the available RAM on your machine to execute computational tasks; however, after that work is completed and a dataset is returned, Tableau performs additional processing before rendering the results.

Let us look at the definition of table calculations, as follows:

A table calculation is a function performed on a cached dataset that has been generated as a result of a query from Tableau to the data source.

Let us consider a couple of points regarding the dataset in the cache mentioned in the preceding definition.

This cache is not...

Directional and non-directional addressing

We will begin this section by listing the different table calculation functions.

Tableau offers a wide range of table calculations, but if we narrow our consideration to unique groups of table calculation functions, we will discover that there are only 11:

...

Table Calculations

LOOKUP

PREVIOUS VALUE

RUNNING

WINDOW

FIRST

INDEX

LAST

RANK

SIZE

TOTAL

Application of functions

So far, we have covered the first of our two major questions: What is the function? Now we will proceed to the next question: How is the function applied?

Let’s try to understand that question via the following three options, which are all applications of the INDEX function:

Figure 5.15: Applications

The INDEX function is used in each of these three screenshots; however, it is applied differently in each. The first and second screenshots both display 1, 2, and 3, but differ directionally. The third screenshot ranges from 1 to 9. So, how is INDEX being applied in each case?

Answering this question can be confusing because Tableau uses different terminology. Within Tableau itself, the way a table calculation is applied may be referred to as running along, moving along, compute using, or partitioning (scoping) and addressing (direction). For our purposes, we will utilize the terms partitioning and addressing, which we will define here...

Guidelines: a reminder

Throughout this chapter, I laid out a series of partitioning and addressing guidelines. Here they are again as a quick reminder:

• Do not use the out-of-the-box partitioning and addressing settings provided by Tableau, including Table (across) and Pane (down). Force yourself to click Specific Dimensions and manually define the partitioning and addressing so that you clearly understand how every table calculation is applied.
• Place all needed dimensions on the desired shelves before setting partitioning and addressing for table calculations.
• It is not necessary to choose the bottom dimension in the At the level drop-down menu. It is always identical to Deepest.
• When addressing multiple dimensions for a table calculation, the order of addressing will usually reflect the order of dimensions on the Rows and/or Columns shelves.

Summary

In this chapter, we explored the inner workings of table calculations. We began by considering what the partition and direction of a table calculation are. In the thereafter following sections, we distinguished directional and non-directional addressing of table calculations, which eventually lead us to the Edit Table Calculation interface, and we identified it as the place to change partition and addressing (also called the scope and direction). As we explored, we surveyed each unique group of table calculation functions except for the Script_ and Model_ functions, which will be covered in more detail in Chapter 15, Integrating Programming Languages. We learned how to apply these functions to a view through partitioning and addressing, where partitioning can be seen as the scope and addressing as the direction of the calculation.

We have seen examples where we compared measures over different time periods, like weekly profit numbers, or comparing a part to the whole....