July 18, 2012
Tableau Tip: Using a parameter for dynamic nested filtering
Suppose you have two dimensions, Customer Segment and Product Sub-Category, and you are displaying sales for each in a bar chart like this:
You want to look at only the top X Product Sub-Categories within each Customer Segment based on Sales. Naturally, you click on the Sort Descending button, but, uh oh, you get this:
What happened? The bars are not sorted by Product Sub-Category within each Customer Segment. They’re actually sorted by the total of the Product Sub-Category across the entire table.
What we really want is for the Product Sub-Categories to be sorted within each Customer Segment. To do that, you need to create a set.
Step 1 – Choose the two fields you want to create the set with, then right-click and choose Create Set.
Step 2 – Give the set a name if you want, rearrange them to the proper order and click ok.
NOTE: If the columns are not in the order you want, then simply drag them left and/or right. In our example, we want Customer Segment first because that is our first level dimension.
Step 3 – Drag your new set into the view by placing it between the Customer Segment and Product Sub-Category dimensions on the Rows shelf.
Step 4 – Clear the sort from the Product Sub-Category dimension, then right-click on the Set on the Rows shelf and choose Sort. Set the sort order to descending by Sales.
Your chart is now sorted by Product Sub-Category within each Customer Segment.
Step 5 – Clean up the chart by right-clicking on your set in the Rows shelf and uncheck Show Header.
That still leaves some extra grid lines. To clean them up go to the menu and choose Format => Borders. Move the slider on the Row Divider level one spot to the left.
You should now have a nice clean bar chart.
But, I want to see only the top X Product Sub-Categories within each Customer Segment. Let’s continue on.
Step 6 – Create a parameter to allow the user to choose between the top 1 and the top 10.
Step 7 – Create a “rank” field that will show you the rank of each Product Sub-Category within each Customer Segment.
Step 8 – Add the Rank measure to the Level of Detail shelf.
Step 9 – Edit the Rank field by right-clicking on the Rank field and choosing Edit Table Calculation. Choose Advanced from the Computing using list.
In the Advanced window, move both fields into the Compute Using box and change the Order Along section to Sales descending. Click OK.
Now updated the Rank table calculation to the following:
Basically what this is saying is that the Rank field should compute for each Product Sub-Category within a Customer Segment, then it should restart counting when it gets to the next Customer Segment.
Step 10 – Create a calculated field that uses the Top X parameter to filter the number of Product Sub-Categories that should be displayed within each Customer Segment.
This creates a true/false filter.
Step 11 – Add the Top X ? true/false calculated field to the Filter shelf and select True.
Step 12 – Show the Top X parameter control created in Step 6 and you’re done!
You can now use the Top X control to pick how many Product Sub-Categories you want to show within each Customer Segment.
Download the Tableau Workbook here.