TopN (quick filtering) filters the result set returned from a database based on numerical values to quickly select the top or bottom N records. In data analysis, you can filter by dimensions or measures to segment data at different granularities to support decision-making. This topic describes how to configure TopN filters.
Prerequisites
You have created a dashboard. For more information, see Create a dashboard.
You have selected a dataset and configured the chart fields. For more information, see Configure chart fields.
Usage
This analysis method is based on the result set. The current limit on the public cloud is 10,000 rows. If your query returns more than 10,000 rows, the results may be inaccurate.
The following table lists the chart types that support TopN.
Calculation method
Description
Chart types
Overall TopN
Applies a TopN filter to the entire dataset.
Line/area charts: line chart, area chart, stacked area chart, 100% stacked area chart, and combo chart.
Column/bar charts: column chart, stacked column chart, 100% stacked column chart, radial bar chart, leaderboard, bar chart, stacked bar chart, 100% stacked bar chart, waterfall chart, bullet chart, box plot, and histogram.
Bubble/scatter charts: bubble chart, scatter plot, and faceted scatter plot.
Table charts: crosstab and heatmap.
Pie/ring charts: pie chart, rose chart, radar chart, and treemap.
Geo charts: choropleth map, bubble map, heat map, flow map, and symbol map.
Indicator charts: indicator card and indicator trend chart.
Funnel/conversion charts: funnel chart and comparison funnel chart.
Other charts: word cloud.
In-group TopN
Groups data by a specified dimension and applies a TopN filter within each group.
Line/area charts: line chart, area chart, stacked area chart, 100% stacked area chart, and combo chart.
Column/bar charts: column chart, stacked column chart, 100% stacked column chart, bar chart, stacked bar chart, 100% stacked bar chart, bullet chart, box plot, and histogram.
Bubble/scatter charts (the X-axis cannot be a measure): bubble chart, scatter plot, and faceted scatter plot.
Table charts: crosstab and heatmap.
Access TopN settings
In the Fields pane of the chart, find the measure that you want to configure. Click the 
icon to its right and select Advanced Calculation > TopN. Configure the TopN calculation method based on your business scenario.
When you select Custom as the calculation type, the options available for a crosstab differ from other charts. The following table describes the differences.
Chart type | Calculation type | Limitation | Calculation logic |
Crosstab | TopN by column | The Rows area must contain a dimension field. | Treats each column as an independent unit and retains the TopN values from that column. |
TopN within a group by column | The Rows area must contain two or more dimension fields. | Groups the data in a single column by a selected dimension and then applies a TopN filter within each group. The grouping logic is as follows: groups are formed by combining all dimension fields in the Rows area, from the first dimension to the selected grouping dimension. For example, in the following figure, the data is grouped by the Region and Province fields. | |
TopN by row | The Columns area must contain a dimension field. | Treats each row as an independent unit and retains the TopN values from that row. | |
TopN within a group by row | The Columns area must contain two or more dimension fields. | Groups the data in a single row by a selected dimension and then applies a TopN filter within each group. The grouping logic is as follows: groups are formed by combining all dimension fields in the Columns area, from the first dimension to the selected grouping dimension. For example, in the following figure, the data is grouped by the Region and Province fields. | |
Other charts | Overall TopN | N/A | Applies a TopN filter to the entire dataset. |
In-group TopN |
| Groups data by a dimension field and applies a TopN filter within each group. The grouping logic is as follows:
|
Examples
You can apply TopN filters to both measures and dimensions.
Apply TopN to a measure
To view the top three records by order amount, configure the order amount measure as follows:
Log on to the Quick BI console.
Navigate to the dashboard editing page as shown in the following figure.
In the top menu bar, click the Add Chart icon (
) and create a crosstab.In the Fields tab of the chart designer, configure the data for the crosstab.
Click the
icon next to the measure, and select Advanced Calculation > TopN > Top 3.
Click Update.
The crosstab now displays the top three records, sorted by order amount.
Apply TopN to a dimension
To view the top three records by order amount within each region, apply an Advanced Calculation > Single-dimension TopN > Top 3 filter to the region dimension. This shows the top three records for each region, such as Northeast, North China, and South China.
Customize TopN
The TopN filter retains the top N records. For example, selecting Top 5 retains the first five records. You can also customize the Calculation Type, Order, and Number of items. The available calculation types for a crosstab differ from those for other charts. For more information, see the comparison table in the Access TopN settings section.
The following table provides examples of different calculation types in a crosstab.
Calculation method
Configuration
Example
TopN by column
Treats each column as an independent unit and retains the top 3 records based on order amount within that column.
TopN within a group by column
In this example, the first dimension in the Rows area is Region, and the grouping dimension is Province. The City data is grouped by its corresponding Province and Region. The filter then displays the top 3 cities by order amount within each province.
TopN by row
Treats each row as an independent unit and retains the top 5 records based on order amount within that row.
TopN within a group by row
In this example, the first dimension in the Columns area is Region, and the grouping dimension is Province. The Product Type data is grouped by its corresponding Region and Province. The filter then displays the top 2 product types by order amount within each province.
Special cases and handling
Totals and subtotals
Totals and subtotals are incompatible with TopN filtering. Applying a TopN filter automatically disables total and subtotal calculations.
Hybrid row and column layout
In a hybrid row and column layout, the filter is applied to the dimension value of the first column by default. In the following example, the filter is based on the order amount for the Northeast region.
Null values
In TopN filters, a null value is treated as the minimum possible value. If you have 10 data records and apply a Top 5 filter (descending order), null values are filtered out. If you apply a Bottom 5 filter (ascending order), null values are retained.
Multiple TopN filters
You can have only one active TopN filter at a time. Applying a new TopN filter overwrites the previous one.
Multiple advanced calculations
You can apply only one type of advanced calculation to a measure at a time. For example, if you apply a TopN filter, it replaces any existing ranking calculation.