The top/bottom filter restricts your results to the highest or lowest results. For example, you can create queries such as 'top five products by profit' or 'top 20 sales reps by revenue generated'. This can be useful if you have several results, but your chart type functions best with a smaller number.
There are two ways to create a top/bottom filter:
Top/bottom result manipulation
The top/bottom result manipulation is a simple way to break down a long list of results to the highest and lowest.
- In the Query Builder, click the Result manipulation icon ().
- Select the Top bottom option.
- Enter the number of top and bottom results you would like to see.
- Select the metric to use.
- Select your strategy. If you have attributes on both Rows and Columns, you can choose the appropriate section. If you have multiple attributes, and want to see the top/bottom for individual attributes, you should select the per block strategy.
If you select Auto for the strategy, BIME will automatically choose the most obvious way to apply the filter.
Additionally, you can select Aggregate values and Aggregate filtered elements.
Aggregate values is useful if you have attributes on both Rows and Columns or a chart displaying results over time. By default, the calculation is executed on the first occurrence of the metric. If you select Aggregate values, the calculation will be executed on the total of the metric's results.
If you select Aggregate filtered elements, all results outside your specified top/bottom range will be combined into an other category. This is particularly useful when you don't want the details of smaller results, but still want overall totals and percentage shares to be accurate. The example below only displays the top five countries, while the smaller results are aggregated into a other category.
Top/bottom attribute calculation
If you want your top/bottom filter to apply to other metrics than those on the frame, you can use a top/bottom attribute. Top/bottom attributes can be used in all queries created from the connection and function best on larger datasets.
For example, if you want to analyze the average income in countries with the highest population, you could use a top/bottom attribute. In the image below, only the metric Average income is displayed on the graph, while Population by Country is calculated as a top/bottom attribute.
- In the Query Builder, click the Calculation engine icon ().
- Select the Top/bottom attribute option.
- Select your metric and aggregator.
- Select the number of top and bottom values to display.
- Select an attribute.
- Add your attribute to either Columns or Rows.
You also have the option to Compute according to the filters in the query. If you have any filters applied to the metrics and attributes of your query, this option will take them into account.