In our second week at the Data School Down Under we learnt about LOD’s (level of detail calculations) and table calculations. As part of our weekly presentations, our task was to find a Workout Wednesday challenge on either LOD’s or table Calculations to recreate and discuss in front of an audience. This blog will serve as both a walkthrough of me building out the #WOW dashboard, as well as lesson on table calculation fundamentals.
What is a table calculation?
Table calculations are inbuilt calculations that Tableau computes based upon specific combinations of aspects within a table. You can use a table calculation to find the difference, percentage and percentage difference from a specified value, create a rank, find the percentage of total, running total or moving calculation. They are an extremely handy tool that every Tableau designer should know.
Tableau essentially creates a virtual table based upon the specific dimensions and measures you pull onto the canvas. From this virtual table, Tableau computes the calculations. I’ve constructed one of these tables to demonstrate a running total and the variations on computations that can be applied.
To create a table calculation you need to click on a measure (can only work with a measure) and select “Create table calculation”. This will bring you to the table calculation configuration menu.
For this example, I will select running total as the calculation type. A running total means whatever measure will be accumulatively aggregated in the direction you chose. By this, I mean if we select to calculate a running total and compute using the table (across), it will accumulate the measure total as it goes across a row, and resets each row (the right most cell being the highest accumulative value).
If we compute using table (down), it will accumulate downwards using each column, with the bottom most cell being the total cumulative value and the running total resetting each column.
The table (across then down) and table (down then across) act similarly to their original counterparts except that they do not reset or restart the running total for each row (for across then down) or column (for down then across).
For my WOW challenge I had to create a multi-line chart which demonstrated the percentage difference of a company’s average closing stock price from a specific set date (in this case determined by a parameter) to the final average closing stock price listed in the data.
The first step is to drag on all the foundational components that make up this chart:
- Month (along the x-axis)
- Avg Close Price (along the y-axis)
- Company (on both the text and colour)
Now, rather than heading straight into the table calculation, we need to take a little sidestep in order to set up our parameter and link it to a reference line. You can set up the parameter later by clicking “Create Parameter…” above the tables, but this way saves any little errors when selecting the data type (more on this below).
In the “Analytics” pane in the top left, under the “Custom” header, you can drag on a “Reference Line” onto the chart. You will then be prompted with a tab to edit and configure the line. Make sure to select “Create a New Parameter…” as the value, which will automatically take you to the tab where you can create a parameter. Remember how I said making sure the data type was correct when creating a parameter involving dates? When you create a parameter this way, you will notice how the data type is already set as DateTime and doesn’t allow you to change it. This means that when you inevitably forget to input the correct data type manually, it is already set and ready to go! Now, since our x-axis is dealing with continuous months in which the data is rolled up to the start of the month, you need to make sure you set the current value as the exact start of a month. You can pick any date and time, but since the data is rolled up to the start of the month, nothing will be shown for any other date or time asides from that. I just find it nicer to immediately see it work on the chart, rather than having to wait until I set the parameter date later on down the track. And then of course, once you go back to the edit line tab, you can change the label and tooltip accordingly.
Now for the table calculation!
Remember we are adding this to the average close measure, and want to find out the percentage difference from a certain point. Rather than using the auto-generated table (across) or cell computations, we specifically only want to compute the difference based upon a “specific dimension”. In this case, we want to find the percentage difference based upon a point in time, so I selected the month of date. It is important to not tick the company in this instance. The way I was taught about using specific dimensions is that you tick the one you want the calculation to be based off, and you leave the one that you want it to restart each calculation for. So for this, I want to calculate the percentage difference for a single company on a certain date, then restart the calculation for the next company, and so on.
One final point is making sure to select your recently created reference line parameter as the “relative to” point. That way, the percentage difference will be from whatever point you set that date parameter to, adding a bit more interactivity to the chart.
All that is now left to do it the formatting!
I wanted the percentage difference value to be shown on the line and change dynamically, so I dragged the average close table calculation down on to the label. Now, this is a cool little tip I learnt to achieve the up and down arrows. If I right click on that table calculation label and select format, it will bring up the formatting pane. Since arrows are not natively inbuilt into Tableau’s formatting repository, I needed to create a custom number format using the Unicode arrows (you can just copy and paste them from google!). The syntax for this custom format is anything left of the semi-colon is for your positive values, and anything to the right of that represents your negative values. I only wanted to have one decimal place, so I notated the numbers as “0.0”. If I wanted 3 decimal places I would notate it “0.000”. And then you can think of the arrows and % sign as extra add-ons around the numbers.
Finally, under the “Label” button, I made sure to change the font colour to match the mark colour. This is optional, but I find it minimises any possible mix-ups between companies if the formatting somehow goes haywire!
And there you have it! The WOW challenge that incorporates the “percentage difference to” table calculation!