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

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

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
• Amount

You can leave these filters at the same filter scope, for example, unpinned as canvas filters. However, if you set the Amount filter to filter on a specific attribute, 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_example2_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_example2_date.png
  
  
• Click Amount and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Product for the By row. The visualization doesn’t display any data.
  
  
  Description of the illustration filters_topbottomn_example2_amount.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 de-select Product for the By row in the filter dialog so it uses All Attributes in Visual.

Description of the illustration filters_topbottomn_example2_filterbar_ok.png

Now the visualization displays the top 4 products in the Produce department in 2020 Qtr 2 based on the amount of inventory, which is what you’re looking for. This is because the All Attributes in Visual option acts like a visualization filter which means the Top N filter is applied after the other two filters.

Option: Add the Top N Filter Directly to a Visualization

Another way to achieve this is to use Amount as a visualization filter.

Drag the Department and Date columns to the filter bar. Drag the Amount column to Filters in the Grammar pane for your visualization.

• Select Produce for the Department. The visualization displays only the products in the Produce department.
• 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 Amount and in the filter dialog, change the filter type to Top Bottom N. Then type 4 in the Count row and select Product for the By row.

The visualization displays the top 4 products in the Produce department in 2020 Qtr 2 based on the amount of inventory. This is because the filters in the filter bar are applied first, then the visualization filter.

Description of the illustration filters_topbottomn_example2_vizfilter_ok.png