Skip to main content

Rank

Overview

Use Cases

Rank calculation sorts a group of indicators using a specific algorithm or formula and assigns each indicator a rank, making it easier to compare the size of values.

For example, when evaluating sales performance across different regions, Rank can clearly show the relative sales amount of each region.

Feature Overview

In the measures area, click the field to rank and choose Advanced Calculation - Rank from the drop-down menu to open the Rank settings dialog. The system ranks values based on the current grouping. Users can choose ascending order or descending order.

Quick Start

You can configure the Rank type, calculation dimension, Rank method, sort order, and TOP N. These options are described below.

  1. Rank Type: Supports Row/Column Rank (global Rank), Dimension Item Rank (global Rank), and Intra-Dimension Rank (group Rank).
  2. Calculation Dimension: Depending on the Rank type, you can choose different calculation dimensions, such as by row, by column, or a specific dimension field.
  3. Sort Order: Supports High to Low (descending) and Low to High (ascending).
  4. Rank Method: Supports RANK, DENSE_RANK, and ROW_NUMBER. If no duplicate ranks exist, the three methods return the same result. If duplicates exist, the results differ. Choose the method that best matches your scenario, as described below:
Rank MethodDescription
RANKAssigns the same rank to equal values, and duplicate values occupy Rank positions. For example: (1, 2, 2, 4)
DENSE_RANKAssigns the same rank to equal values, but duplicate values do not occupy Rank positions. For example: (1, 2, 2, 3)
ROW_NUMBERAssigns a unique rank to each value according to the Rank order. For example: (1, 2, 3, 4)

The effect is shown below:

Global Rank

Global Rank is implemented by setting the Rank type to Row/Column Rank or Dimension Item Rank. It supports Rank by row, by column, and by a specified dimension.

By Column means Rank the values corresponding to all dimension items. By Row means Rank the values corresponding to all comparison items. Specified Dimension means Rank values globally at the selected dimension level.

By Column

The following example analyzes the nationwide sales Rank of each province.

  1. Create a standard table. Drag Region and Province into Dimensions, and drag Sales Amount into Measures, as shown below:

  2. Click Sales Amount and choose Advanced Calculation - Rank. The Rank configuration dialog appears, as shown below:

  3. Set the Rank type to Row/Column Rank, the calculation dimension to By Column, and the order to High to Low. This ranks provinces nationwide by sales amount.

By Row

The following example analyzes the Rank of sales amounts for different product categories within each region.

  1. Create a standard table. Drag Region into Dimensions, Product Category into Comparison, and Sales Amount into Measures, as shown below:

  2. Click Sales Amount and choose Advanced Calculation - Rank. The Rank configuration dialog appears, as shown below:

  3. Set the Rank type to Row/Column Rank, the calculation dimension to By Row, and the order to High to Low. This ranks the sales amounts of product categories within each region, as shown below:

Rank by Specified Dimension

Rank by specified dimension is implemented by setting the Rank type to Dimension Item Rank.

The following example analyzes both the Rank of province sales within each region and the Rank of total sales by region.

  1. Create a standard table. Drag Region and Province into Dimensions, and drag Sales Amount into Measures, as shown below:

  2. Click Sales Amount and choose Advanced Calculation - Rank. In the dialog, set the Rank type to Dimension Item Rank, choose Province or Region as the calculation dimension, and set the order to High to Low.

    When Region is selected, Rank is calculated at the region level.

    When Province is selected, Rank is calculated globally at the province level, which is equivalent to Global Rank - By Column.

Intra-Group Rank

Intra-group Rank is implemented by setting the Rank type to Intra-Dimension Rank. The following example analyzes the Rank of province sales amounts within each region.

  1. Create a standard table. Drag Region and Province into Dimensions, and drag Sales Amount into Measures, as shown below:

    14.png

  2. Click Sales Amount and choose Advanced Calculation - Rank. In the configuration dialog, set the Rank type to Intra-Dimension Rank, choose Region as the calculation dimension, and set the order to High to Low.

    About the selectable range of calculation dimensions: when there are N dimensions, the last dimension has no more detailed dimension below it, so its Rank result is always 1 and is not meaningful. Therefore, only the first N-1 dimensions are supported.

    For example, when there are 3 dimensions, either of the first 2 can be selected. When there are 2 dimensions, only the first can be selected.

    The system first groups data by region and then ranks province sales amounts within each region from high to low, as shown below:

TOP N

In addition to intra-group and global Rank, the Rank results also support TOP N filtering and analysis.

After Rank is configured, you can set a custom number in TOP N. The custom number must be a natural number between 1 and 100.

For example, if the user wants to see the top 3 provinces by nationwide sales amount, select Custom Number in TOP N and enter 3.

This filters the data to show the top 3 provinces by nationwide sales amount.

  1. If subtotals are displayed, subtotals can also be ranked.

  2. In TOP N Rank settings, you can enable Apply to Subtotals and Totals.

    • If enabled, only subtotals that meet the TOP N condition display Rank values; others display null.
    • If disabled, Ranks are displayed for all subtotals.

Limitations

  • Detail tables do not support Rank.