Iravati Abhyankar, BI Consultant & Trainer, CRG Solutions
We have plenty of applications in day to day scenarios where Tableau’s Table Calculations need to be used. And the fact that Tableau has Quick Table Calculations that are available just in the context sensitive menu (on the right-click) on Measures makes those very easy and friendly.
At the same time, it’s interesting to note how Tableau allows us to define complex calculations and better control with Table Calculations like WINDOW_SUM() and INDEX() and SIZE() and PREVIOUS_VALUE() etc.
Listed below are some usecases that I find fascinating. The ease of use gets highlighted in these.
The Usual
Difference, Percent Difference, Year To Date, Year over Year Growth percentage and even YTD Growth are commonly required and available in the Quick Table calculations if you have the right Time Dimension columns included in the view.
Percent of Total is another must-have to show contribution, comparison and highlight certain extra-ordinary figures or observations.
For Year to Date and YoY to work, we need to have Year as a separate dimension and not a continuous MY Axis. The comparison by default is with previous but practical scenarios expect the monthly figures (amounts, counts,..) to be compared against same month last year rather than previous month. This makes sense because there is seasonality factor. For example, sales around festival months should not be compared against prior months but against festival months of the prior year.
In terms of Partition & Addressing, Month is partition and comparison is ALONG Year (FOR EACH Month).
Hence it is very important to learn the terms
- Across / Down
- Table / Pane / Cell
- Previous / Next / First / Last
Compute Using that allows us to select the dimension(s) that define the scope.
Most of the trouble shooting when the amounts are not right need to focus on the above mentioned settings for the table callculation. When you hover over the small triangle that shows up on the pill when table calculation is applied, it shows a brief description of what exactly is being displayed or how it is being arrived at!
Table Calculations as a filter
Rank
Rank is commonly used instead of Top N when there are multiple dimensions involved. As seen in the example below: Top 2 Subcategories within each Region are expected to be shown. Label shows rank per Pane (within region) and SubCategory Top 2 by Profit is used as a filter. The result is not correct – if we compare the values WITHOUT filter.
Instead we should be using Rank (which is on the label) in the filter as in the image below.
Lookup Filter to show correct Percent of Total
You must have come across Lookup filter.
When Customer Segment-wise contribution is shown but user expects a Segment filter as well in the view.
The default way to present being as in Image 1. But the moment user filters one or more of the segments, the Percent of Total is auto-adjusted to give 100% divided among remaining segments.
That is where a Lookup Filter comes in handy. In place of Segment filter, we will define a field that will act as a filter and allow user to filter segments without impacting the percent of total.
Secondary Calculation for Pareto Analysis
I espcially like the way Tableau lets us define Secondary Table Calculation. The most common usecase being of Pareto Analysis.
In order to find the 20% (say Customers) that contribute the 80% (of say Profit),
Customers are listed in descending order of Profit. Starting from the most profitable customer, we then computer the Running Total. Here’s where secondary calculation is required to check the percent of total upto that point. That means, after each customer’s profit getting added to the Running Total, we eant to observe at what point does it reach 80% of the total.
Moving calculations
Moving average or sum is another of functionality that is made easy to configure and apply by Quick Table Calculation. An interesting application of “wrap around” moving average is mentioned in my old blog
https://www.crgsolutions.co/blogs/moving-calculations-in-tableau-wrapped-around/
And Finally, Previous_Value() Function
Although Tableau does not have looping (that involves “for” or “while” kind of coding), it let’s us accomplish certain of those tasks with the help of this function Previous_Value()
Some of the common applications are:
- Imputation – replacing Nulls with earlier value
- Running Sum and even running Product
- Calculating Compound Interest
For compound interest calculation, we need to always consider prior amount on which interest should be applied and not the original principal amount. Principal is kind of moving .. or growing in this case.
Hence if 8% interest is to be applied every year
- PREVIOUS_VALUE(1000) * 1.08
- Will return the resulting amount for the current year.
- The value 1000 inside the paranthesis is replacement if earlier value is Null.
Although this is not an exhaustive list of Table calculations, I thought of sharing the ones that I needed to use very frequently over the years. I hope you all learn and try and apply all these calculations.
Recent Posts
How Alteryx Supports Workforce Analytics for Talent Retention Amidst Global Layoffs
With the prevailing uncertainty in the job market, global layoffs have become common. In this scenario, talent retention has become the critical priority for organizations planning to maintain operational stability. Organizations need to understand their workforce requirement, predict turnovers and...
How data analytics can help take sustainable actions against climate change
Globally, climate change is visible in all aspects today, and it is one of the pressing issues, and this needs to be addressed. And data analytics has emerged as a powerful tool in finding sustainable solutions. It works with large...
How to map your way to better business decisions with the power of spatial analytics in Alteryx
According to S&P Global Market Intelligence Study, 96% of businesses highlight the importance of data utilization in their decision-making processes. (Ref: https://www.nearshore-it.eu/articles/data-driven-decision-making/) This shows the importance of knowing where your data comes from and what your data is telling you...
Leave a Comments
You must be logged in to post a comment.