Example – Use a Top N Filter to Show the Top Attribute Values Based on a Measure

This example shows how to create a Top N filter using an attribute column to show a subset of the top values for that attribute based on a measure.

In this example, you have a table visualization showing the inventory of some products for a grocery store. There are 10 products in the Dry Goods department and 10 in the Produce department.

Description of the illustration filters_topbottomn_example_inventory.png

You want to refine the visualization so that it shows the top 4 products in the Produce department based on the number of units available, and you want to target a specific time period.

Option: Add the Top N Filter to the Filter Bar

Add the following filters by dragging the columns to the filter bar:

• Department
• Date
• Product

If you leave all three filters at the same filter scope in the filter bar, for example, unpinned as canvas filters, you won’t get the result you want. Here’s what happens:

• Select Produce for the Department. The visualization displays only the products in the Produce department.
  
  
  Description of the illustration filters_topbottomn_example1_produce.png
  
  
• Select 2020 Qtr 2 for the Date. The visualization displays only the amount of products that were in the Produce department inventory in the second quarter of 2020.
  
  
  Description of the illustration filters_topbottomn_example1_date.png
  
  
• Click Product and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Amount in the By row. The visualization doesn’t display any data.
  
  
  Description of the illustration filters_topbottomn_example1_product.png
  
  

In this situation where the Top N filter is a canvas (unpinned) filter in the filter bar, it's applied independently of the other canvas filters. The result set of the Top N filter is then combined with an AND operation with the other canvas filters, which results in no data.

Instead, you can pin Department and Date to change them to workbook filters, which means they are at a higher scope than the Product filter. Now, those filters are applied first, and then the Top N Product filter is applied, which produces the result you expect.

Description of the illustration filters_topbottomn_example1_filterbar_ok.png

Option: Add the Top N Filter Directly to a Visualization

Another way to achieve this goal without creating workbook filters (if you don’t want Department and Date to apply to all your canvases), is to use Product as a visualization filter.

Drag the Department and Date columns to the filter bar. Drag the Product column to Filters in the Grammar pane for your visualization. Here’s what happens:

• Select Produce for the Department and select 2020 Qtr 2 for the Date. The visualization displays only the amount of products that were in the Produce department inventory in the second quarter of 2020.
• In the visualization, click Product and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Amount for the By row.
• The visualization correctly displays the top 4 products in the Produce department in 2020 Qtr 2 based on the amount of inventory because the Product filter is applied to the data after it’s already filtered by Department and Date.
  
  
  Description of the illustration filters_topbottomn_example1_vizfilter_ok.png