TopN (quick filtering) filters results returned from the database based on numerical values, making it convenient for users to screen data and quickly select a specific number of maximum or minimum data entries. In data analytics, you can filter through dimensions or measures to segment at different granularities, helping you make better decisions. This topic describes how to set up TopN.
Prerequisites
You have created a dashboard. For more information, see Create a Dashboard.
A dataset is selected and chart fields are configured. For more information, see Configure Chart Fields.
Usage Notes
This analysis method is based on the returned result set calculation, with a current limit of 10,000 in the public cloud. If the total data returned exceeds 10,000, the calculation results may not meet expectations.
The charts that support TopN are shown below.
TopN calculation method
Description
Supported chart types
Overall TopN
Performs TopN filtering on the overall data.
Line/Area charts: Line chart, Area chart, Stacked area chart, 100% stacked area chart, Combination chart.
Column/Bar charts: Column chart, Stacked column chart, 100% stacked column chart, Circular column chart, Ranking board, Bar chart, Stacked bar chart, 100% stacked bar chart, Waterfall chart, Bullet chart, Box plot, Histogram.
Bubble/Scatter charts: Bubble chart, Scatter chart, Facet scatter chart.
Table charts: Cross table, Heat map.
Pie/Ring charts: Pie chart, Rose chart, Radar chart, Treemap chart.
Geographic charts: Color map, Bubble map, Heat map, Flow map, Symbol map.
Metric charts: Metric card, Metric trend chart.
Funnel/Conversion charts: Funnel chart, Comparison funnel chart.
Other charts: Word cloud.
Group-based TopN
Groups data by a specific dimension and performs TopN filtering within each group.
Line/Area charts: Line chart, Area chart, Stacked area chart, 100% stacked area chart, Combination 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, Histogram.
Bubble/Scatter charts (X-axis cannot be a measure): Bubble chart, Scatter chart, Facet scatter chart.
Table charts: Cross table, Heat map.
Configuration Entry
In the Fields panel of the chart, find the measure field that requires TopN calculation, click the icon on its right, and select Advanced Calculation > TopN from the dropdown list. Then set the TopN calculation method according to your business scenario.
When the calculation type is set to Custom, the calculation types available for cross tables differ from other charts, as explained below.
Chart type | Calculation type | Limitation | Calculation logic |
Cross table | Column TopN | Dimension fields in the Row area | Treats each column as an independent analysis unit and filters out TopN data in an entire column. |
Column group-based TopN | Two or more dimension fields in the Row area | Groups single column data according to the selected dimension and performs TopN filtering within the groups. The grouping logic is: starting from the first dimension in the Row area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the image below, the Region and Province fields will be used for grouping operations. | |
Row TopN | Dimension fields in the Column area | Treats each row as an independent analysis unit and filters out TopN data in an entire row. | |
Row group-based TopN | Two or more dimension fields in the Column area | Groups single row data according to the selected dimension and performs TopN filtering within the groups. The grouping logic is: starting from the first dimension in the Column area up to the selected grouping dimension, all dimensions in between are treated as a whole for grouping operations. For example, in the image below, the Region and Province fields will be used for grouping operations. | |
Other charts | Overall TopN | / | Performs TopN filtering on the overall data. |
Group-based TopN |
| Groups by dimension fields and performs TopN filtering within groups. The grouping logic is:
|
Examples
Both measures and dimensions support TopN settings.
Setting TopN for measures
If you need to view the top three data entries by order amount, you can set up the measure (Order amount) by following these steps.
Log on to the Quick BI console.
Follow the steps shown in the image below to enter the dashboard editing page.
In the top menu bar, click Add Chart, find the
icon, and create a Cross Table.
In the Fields tab of the chart design, configure the cross table data.
Click the
icon on the right and select Advanced Calculation > TopN > Top3.
Click Update.
The cross table will now sort by order amount and display the top 3 data entries.
Setting TopN for dimensions
If you need to view the top three data entries by order amount for each region, you can set Advanced Calculation > Single Dimension TopN > Top3 on the dimension (Region). This will filter out the top three data entries for each region such as Northeast, North China, South China, etc.
Custom TopN
TopN retains the top N data entries. For example, selecting Top5 will retain the top 5 data entries. You can also customize the TopN Calculation Type, Order, and N Value. The calculation types available for cross tables differ from other charts. For details, see the comparison table in Configuration Entry.
Examples of different calculation types in cross tables are shown below.
Calculation method
Configuration description
Example effect
Column TopN
Treats each column as an independent analysis unit and filters out the top 3 data entries by order amount in an entire column.
Column group-based TopN
In this example, the first dimension field in the Row is Region, and the grouping dimension is Province. Therefore, City is grouped by its Province and Region, and the top 3 cities by order amount are displayed for each province along with their order amounts.
Row TopN
Treats each row as an independent analysis unit and filters out the top 5 data entries by order amount in an entire row.
Row group-based TopN
In this example, the first dimension field in the Column is Region, and the grouping dimension is Province. Therefore, Product Type is grouped by its Region and Province, and the top 2 product types by order amount are displayed for each province along with their order amounts.
Special TopN calculations and processing methods
Processing of totals and subtotals
Totals and subtotals do not participate in quick filtering and cannot coexist with TopN. If TopN is selected, totals and subtotals will automatically be disabled.
Processing of row and column mixed layouts
In row and column mixed layouts, filtering is performed based on the dimension values in the first column by default. For example, in the example below, filtering is performed based on the order amount in the Northeast region.
Processing of null values
Null values are considered the smallest values in TopN. For example, if there are 10 data entries and you set Top5 (descending order), null values will be filtered out. If you set Bottom5 (ascending order), null values will be retained.
Processing of multiple TopN settings
Multiple TopN settings cannot take effect simultaneously. The later setting will replace the previous TopN setting.
Processing of multiple advanced calculations
A measure can only have one advanced calculation set at a time. For example, if you set TopN, any previously set ranking will be replaced.