Year-by-year evolutions in Tableau

Today we're going to learn how to make it easy to compare your data against a history in Tableau. This tutorial will use the LAST() and LOOKUP() table calculations and will teach you how to specify a table calculation in Tableau. We will also discuss the case of formatting a number and the captions separated by Measure names. For this tutorial I will use the data source on this link:

https://mkt.tableau.com/Public/Datasets/World_Bank_CO2.xlsx

Here is the expected result:

1 / Year selection parameter

Before we start, we will create a date selection parameter that will allow us to go back in history. Be careful that the field Year is among the dimensions. So slip Year among the dimensions. Then click on Year then => Create => Parameter ...

Tableau will therefore create a parameter with values for all the data present in the field Year (in our case from 1961 to 2011 in view of our Dataset). It is then interesting to slightly format the parameter to display integers only without thousands separators. To do this, click Display Format and then Custom Number. Choose 0 for decimal places and uncheck the thousands separator option. This formatting will indicate that the parameter is a year.

In this way the numbers displayed by Tableau on the parameter will have a formatting without digits after the decimal point, nor spaces between the thousands. We can now use this parameter to filter out years older than the year selected on the parameter. So we create a calculated field that we'll call Filter Years. The calculation is as follows:

[Year] <= [Year parameter]

Drag and drop the filter and keep only the true results. You can then drag the Year field on the columns and the detail in text display as well ... You should get the following result for a selected Year parameter to 1974:

This vision is interesting because it allows us to realize that our parameter allows us to perform a "travel in time". We can thus have a vision of our history to the selected year. But what interests many companies when they are interested in their data is to analyze their performance indicators from one year to the next. Thus, we will build a view that will allow us to compare the CO2 emissions of one year compared to the previous one.

2 / Selection of the last two years

To do this, we will create a calculation that we will name "Last" and which will simply be the LAST () formula . LAST () indicates which are the last records displayed after filtering. Thus we will be able to take the last recordings of years after having filtered the years superior to those selected by our parameter. However, we must tell Tableau that we want to count the last records by year and not by Regions or by Country or any other dimension. Click on Default Table Calculation.

On "Calculate with", select the "Year" option. Thus Tableau will know by default that you will want to compare the last records per year (NB: it will always be possible to change the way you count records sheet by sheet). Click OK and then drag the Last field on the filters and select a range of values from 0 to 1 in the first place to keep only the last two years before the one selected by the filter (we will change that later).

I then slid the field region on the lines and the field CO2 Per Capita on the details. A detailed analysis by region for the last two years is obtained before the chosen year. You should get the following result:

In addition, we must not forget to change the type of aggregation for CO2 per Capita. Indeed, it makes no sense to add an average. We have therefore specified that the default aggregation mode for this field should be the average and not the sum (NB: in our case, the calculation will necessarily be false because we assume that each country has an identical population, but this is just a tutorial after all) :

This result is possible because the LAST () calculation is performed only after filtering by the Dimension Filter (not before). If you would like more information on the order of operations, you can find more information here: https://onlinehelp.tableau.com/current/pro/desktop/en-us/order_of_operations.html

3 / Calculation of the rate of evolution from one year to another

We are almost to the expected result. We now need to calculate the year-on-year change in CO2 emissions per capita.

To achieve this, we will write a new calculation that we will call Evolution CO2 by capita. This calculated field will determine the rate of change in emissions from one year to the next. And in the same way as before, we will tell Tableau that it will have to calculate the evolution from one year to another (and not from one region to another for example).

The calculation is the following (pay attention to parentheses !!):

(SUM([CO2 Per Capita (metric tones)]) - LOOKUP(SUM([CO2 Per Capita (metric tones)]), -1))
/ LOOKUP(SUM([CO2 Per Capita (metric tones)]), -1)

The LOOKUP function calculates the aggregation of its first parameter by shifting the number indicated in its second parameter. Depending on the form your spreadsheet takes, you can tell Tableau into what direction it should look for the calculation. For example a LOOKUP of -1 on Year will allow you to calculate a result for the previous year to that considered. A LOOKUP can also be specified vertically. Thus, a LOOKUP of 1 vertically will look at the value present in the cell below and so on. In the same way as with the LAST () function, we will tell Tableau that it must perform its calculation according to the years. In the same way as before, click Default table calculation and then Calculate with, select Year.

Click OK, Tableau will tell you that it will perform your calculation per year in the yellow banner as follows:

There you go ! We will now display this calculation directly next to the sum of the emissions to be able to compare each year:

Drag the Measure Names field onto the filters and keep only CO2 per capita and Evolution CO2 per capita.

4 / Formatting

Drag Measurement Names on Columns and Measurement Values on Text and Color details. You can change the visualization type for squares. Note that some formatting is necessary because we show the evolution as a percentage. Click Evolution CO2 per capita in the Measurement Values then => Format => Pane => Numbers => Percentage I recommend using separate and inverted color legends for better visual rendering of our data (we do data-viz after all;))

Make sure that the year-to-year evolution calculation corresponds with a calculator. Simply calculate the rate of change from one year to the next by hand. If you do not fall back on the same result, check that the calculation is done along the years. Just click on the delta icon to the right of the calculation Evolution CO2 per capita. Once the verification is done, we only have to hide the previous year to keep only the data for the year selected by the parameter. Last => Edit Filter => Range of values from 0 to 0.

The final result is the expected one:

We have posted a statement of CO2 emissions and its rate of change compared to the previous year. Well done!