VizWiz

Launch, grow, and unlock your career in data

July 18, 2012

Tableau Tip: Using a parameter for dynamic nested filtering

8 comments

Suppose you have two dimensions, Customer Segment and Product Sub-Category, and you are displaying sales for each in a bar chart like this:

image

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:

image

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.

image

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.

image

Step 2 – Give the set a name if you want, rearrange them to the proper order and click ok. 

image

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.

image

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.

image

Your chart is now sorted by Product Sub-Category within each Customer Segment.

image

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.

image

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.

image

Step 7 – Create a “rank” field that will show you the rank of each Product Sub-Category within each Customer Segment.

image

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.

image

In the Advanced window, move both fields into the Compute Using box and change the Order Along section to Sales descending.  Click OK.

image

Now updated the Rank table calculation to the following:

image

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.

image

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!

image

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.

8 comments :

  1. Andy,

    Great posting.

    Another option is to place the Rank field in a quickfilter. This way, steps 10 to 12 are not required.

    I posted a similar workbook here:
    http://public.tableausoftware.com/views/RankWithinPartition/RankWithinPartition?:embed=y

    Thanks!

    ReplyDelete
  2. Thanks Santiago! This is exactly what I love about my blog and Tableau. There are always people that can help make you better.

    We don't exactly get rid of the three steps, but we definitely wouldn't need the parameter.

    As additional information for others:

    1. Remove the parameter and true/false filters from the worksheet.

    2. CTRL+Drag the Rank field from the Level of Detail shelf to the Filters shelf. CTRL+Click maintains the special calculations I added in step 9.

    3. Show the quick filter for Rank

    4. Change it to "At Most" and make sure it's a slider.

    I've updated the workbook that is linked at the bottom of the post with Santiago's suggestions.

    ReplyDelete
    Replies
    1. What is your Tableau version? I have Tableau 10.2.0 but I don't have the option of creating a set from 2 fields.

      Delete
    2. If you don’t have that option you’re doing something wrong. It should be fine with 10.2.

      Delete
  3. Additionally, you do not need the Set pill on the worksheet. When you use the Advanced Compute using, and move multiple pills to the right-side list box, you are making a Set there, and the Set pill on the worksheet is now redundant. Also, in the Edit Table Calculation dialog, you do not need to set the At The Level option, you can leave it at Deepest (not a big deal, but it saves some clicks). To get the visual nested sorting without the Set pill, use the Rank pill as a discrete on the Rows shelf. You also have a few extra redundant pills that I cleaned up as well, see http://public.tableausoftware.com/views/TopXFilteringwithTwoDimensions_0/AltwithoutSet for my edits.

    Alternatively, you can use the Set, and a copy of Customer Segment field. This way you can skip the advanced dialog, and just set the compute using to the Set pill from the right-click context menu (saving even more clicks). I included this as another tab in the workbook. Like you said, there are multiple ways to accomplish things. I would recommend choosing one route instead of trying to do both at the same time.

    One of the minor advantages of using just the Set without the advanced, is the sort button that Tableau adds to the axis is now usable, with a click you can get Top-N, Bottom-N, or no sort (with no-sort not really useful), same functionality could be added using a parameter and calc field as well, just wanted to point out the minor detail.

    If you want to take this to the next level, try making a Top-N with Other, or the super-tricky have the bars be stacked bars, and see the added unexpected complexities when not all possible dimension combinations exist.

    ReplyDelete
    Replies
    1. great stuff! exactly what I was looking for! thanks

      Delete
  4. Can we include data from the fields in parameters instead of hard coding???

    ReplyDelete