Every dashboard or chart created needs to support one common business question which is “What are the Top (or bottom)
N (replace N with 5, 10 etc.)
Products (it can be anything Geo, Channel, Customers etc.)
by Revenue (it can be anything Profit, Margin % etc.)?”
In this blog, we are going understand how to answer this question with the help of filters and parameters.
In this blog we will use cricket data to understand how to do Top N analysis with the help of filters and parameters. In the process we will also understand what a context filter is.
The data can be sourced from howstat.com and formatted appropriately for Tableau’s consumption. This is the first important and often time-consuming step before data visualization and exploration can happen. We have batting data for One Day International (ODI) matches played between years 1971 to 2011 with close to 60,000 data points. The below table gives you a quick overview of important dimensions and measures present in the dataset.
|Player name||Score Rate (runs per 100 balls faced)|
Data Exploration & Visualization
For this article our analysis is going to be around who are the Top N batsmen in terms of run scored.
Step 1 – Connect to the data.
Step 2 – Go to Sheet 1 and analyse/review the loaded data.
Step 3 – Place Players on Row shelf
Step 4 – Place Runs with aggregation method as SUM on column shelf
Step 5 – Sort on Runs
Now this is the crudest way of getting the list of Top N list of players who have scored the highest runs. The problem with this approach is that one sees all the players in the visualization and how one can restrict the visualization to show just top 5 or 10 or 20 players who have scored the most runs.
Step 6 – Put Filter on Player
Apply Top Filter on Player by Field Runs with aggregation method as Sum as shown above to get top 10 players by sum or runs.
The result of applying filter is as shown below with list of Top 10 players who have scored the most runs. As one can see Sachin Tendulkar tops the chart with no marks for guessing followed by Ricky Ponting, Jaysuriya and so on. Please note that this ranking is based on data of matches played till 2011. We have partially achieved what we wanted to do in terms of Top N analysis. How do one can make N vary and give the control of changing in the hands of a user. Let us say if one wants to find the list of Top 5 or 10 or 20 players by runs then the mechanism available in Tableau is to resort to parameter.
Step 7 – Create a parameter
Create a parameter of type integer as shown above and choose to show it. Note that the changing the value of parameter on top right (as shown below) will not have any impact on visualization as yet.
Step 8 – Use parameter in Filter
Choose the parameter (Top N Input) that we created in above step in the filter as shown in the image above. Note that we could have created a parameter from the option provided in this dialog box.
Step 9 – See the parameter in action
Changing the parameter value in slider will now change the visualization to reflect top N players by runs where N is determined by user selection through parameter control.
Step 10 – Put Country in Filter
Let us say we are interested in finding out Top N Indian Players by runs so put Country on Filter and select India as shown above.
After applying Country filter if one looks at the visualization (below) it just shows Sachin Tendulkar whereas parameter is set to 5. That is the visualization should have shown top 5 player of India who have scored the most runs.
Step 11 – Add Country Filter to context
The result of applying country as context filter now correctly produces visualization to show Top 5 batsmen of India by runs.
A context filter is applied to the data source first, and then the other filters are applied only to the resulting records.
So take away from this blog are as below
- Using Filter’s Top/Bottom condition by field and aggregation method
- Using Parameter with Filter
- Using Context Filters
That is it for this time; stay tuned for more learning with Tableau.
One can visit the official Tableau website to find more details about Tableau and its product offering and features.