Chapter 6. Level of Detail and Table Calculations
In the previous chapter, you learned how to use custom calculations to create new fields that add context and insights to the story that you are telling with Tableau Public. In this chapter, we will discuss how you can use table calculations and level of detail calculations to enhance the comparisons that you are making with the data. We will build on several of the calculations that we have created to show you how to make them more dynamic and contextual.
The table calculations are different from the traditional calculations because they are performed locally on the data in the cache, that is, the data that Tableau Public has used to create the visualization. Not all data that is in your data set is in use all the time. The data in the cache is what Tableau Public is using in the memory to render your visualization. Therefore, the data that you have filtered out will not be included in the table calculations.
Level of detail calculations,...
We will continue using the World Development Indicators data source, as some of these examples will be included in the dashboard that we will develop later in this book. You can download the data from the World Bank by visiting http://data.worldbank.org/products/wdi.
Creating quick table calculations
Tableau Public has a feature that enables the rapid creation of a table calculation. After dragging a field onto the visualization, typically a measure, you can aggregate and perform table calculations on dimensions, or click on its context menu and select Quick Table Calculation. There is one limitation to this—table calculations cannot be created on fields that have forecasting turned on.
The following are some of the many different types of quick table calculations, though not all of them are available at the same time, and the options vary by the type of dimensions and measures that are also on the visualization:
Once you have created a table calculation, you can edit it by either clicking on the Context menu on the pill, or right-clicking on it. For each unique table calculation...
One of the questions that we commonly ask about data is, how has performance changed over time? This helps us understand how events of decisions affect outcomes. While understanding the relationship between discrete numbers is helpful, it's even more useful to understand the rate of change, or the percentage change over time. The reason why it's important to understand the rate of change is that, while discrete numbers may appear to be increasing, the actual percentage change from year to year may be declining.
In the following example, we will discuss how to graph the amount of remittances over time and we will create a quick table calculation that shows the percent difference:
On a new worksheet, using the World Development Indicators data source, drag Year from the Dimensions pane to the Columns shelf.
Drag Remittances (USD) from the Measures pane to the Rows shelf.
Exclude years before 1980 by using your preferred method. We selected them on the x axis, hovered over pill...
The graph from the previous exercise calculates the percentage difference from year to year, which means that the value for each year is being computed relative to the previous year. We can ask Tableau Public to calculate the percentage difference from different years by clicking on the Context menu for a table calculation of this type, and then selecting Relative to. We can calculate the change from the first, next, previous (the default one), or last values in a partition.
In the following graph, which is a revision of the previous one, the bar height for each year shows the aggregated percentage difference in Remittances per Capita since 1980, which is the first year in the visualization. You can do this by performing the following steps:
Click on the Context menu for the Remittances per Capita field on the Rows shelf.
Click on Relative to.
Select First, as shown in the following screenshot:
Then, perform the same calculation on GDP (current USD). We changed its use on the Rows...
Manually editing table calculations
Learning how to edit table calculations yourself is an advanced capability, but it gives you the opportunity to create rich metrics, such as the percent difference from an average.
In the first example, we will modify the running sum of Remittances per Capita so that it includes the preceding and next three values, which can be done from the edit table calculation dialogue by entering those numbers in their respective places.
In the second example, we will modify the Remittances per Capita percent difference table calculation to show the percent difference from the average.
Let's begin with the first example. We'll modify the running sum in the following way:
In order to edit the table calculation, duplicate the sheet on which we created the original moving average worksheet, which maintains the integrity of the work that we have already done.
Then, drag the pill for the moving average of Remittances per Capita from the Rows shelf to the Measures shelf, which...
Our examples so far have focused on comparing measures that occur along a time continuum. Table calculations are useful for a comparison between different dimensions as well. Ranking by discrete numbers, as well as percentiles, is powerful. Many people want to know the top or bottom number of members in a dimension. In the next example, the final product shows the bottom 20 percent of the countries in each region according to their availability of improved water sources, and each country will be colored by its percentile of life expectancy.
Start off by creating a basic bar graph from the World Development Indicators data source that shows the maximum percentage of a country's population that has access to improved water sources. Drag Region and Country to the Rows shelf. Then, when we drag Improved Water Source (%) to the Columns shelf, we aggregate it as a maximum rather than a sum. Select MAX based on the assumption that unless there's a major natural disaster (such as floods and...
Window versus running functions
We have briefly discussed the WINDOW functions already. There's also another type of function, called the RUNNING function. The WINDOW functions have specific partitions, that is, they measure either the whole table, pane, or cell, or from a specific number of previous or future values. Alternatively, the RUNNING function compares all the values before the current value in the partition.
In the following example, we graphed the average percentage of the GDP that was from high-tech exports by region and by year in the World Development Indicators data source. This is a simple spark bar graph. We have hidden the header for the y axis in this graph.
We created a calculated field that, for each year, tests whether the average High Tech Exports as a percentage of the GDP is equal to the running maximum, that is, is the value for any year higher than all the years before it. The results are true and false, as shown in the following screenshot:
The RUNNING_MAX function...
The level of detail calculations
The
level of detail calculations (LOD) enable you to tell Tableau Public at exactly which dimensional level you would like to aggregate without having to place that dimension on the visualization. Additionally, you can include or exclude dimensions in the calculations and create calculations that include all the underlying data.
The following are the three types of LOD calculations:
FIXED: This computes a value for the dimension that you specify
INCLUDE: This computes at a dimensional level and is not included in the visualization itself
EXCLUDE: This excludes a dimension that is a part of the visualization
LOD has a big caveat. While you can perform table calculations on them and create aggregations and functions within them, you cannot include table calculations in them.
Each LOD calculation has the following three features that are different from those that we have discussed before:
In this chapter, you learned how to create quick table calculations to add depth and context to your visualizations. You also learned how to edit table calculations, how the different types of table calculations function, and how to address table calculations and create them manually. One of the important features of data analysis is the comparison of a data point to the others around it so that users understand the context of its performance. You learned how to create a basic, and advanced level of detail calculations to aggregate data points that are not visible, and to aggregate at multiple dimensional levels as well.
In the next chapters, which focus on mapping and parameters, we will discuss using many of the concepts that you learned here to give control to your users and illustrate compelling, rich stories with contextually curated visualizations.
