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
- 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.
- 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.
- 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.
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