RANK_AGG

Version

Function Description

Explanation

The following part introduces the four types of ranking rules.

Common Ranking (COMMON)

• Definition: This is the most common ranking method. Each data point is assigned a value based on its position in the ranking. If duplicate values exist, they share the same ranking value, and subsequent ranking values will be skipped.

• Scenario: suitable for basic statistical analysis, especially for directly reflecting the ranking of data in the  set. You can use the common ranking when you need to identify the specific ranking of each data point in the dataset and focus on the equal importance of each duplicate value. For example, in the ranking of exams, if two students obtain the same score, they can share the same ranking, and the subsequent ranking will be skipped, indicating that the two students have the same performance.

• Example: For the data 23, 45, 45, and 76, the ranking values are 1, 2, 2, and 4. 1 is the position of the smallest value, 2 is the position of the second value, and 4 is the position of the biggest value.

Dense Ranking (DENSE)

• Definition: This ranking method is similar to the common ranking, but even if the data values are repeated,  the ranking values will not be skipped. Duplicate values share the same ranking value, and the next non-duplicate value immediately gets the next ranking value.

• Scenario: suitable for maintaining the continuity of data, but not skipping ranking values due to duplicate values in data analysis This type of ranking is useful for scenarios that require a concise and consecutive ranking, such as in sports competitions where rankings need to be displayed in sequence without skipping positions.

• Example: For the data 23, 45, 45, and 76, the ranking values are 1, 2, 2, and 3. 23 is assigned the value 1, 45 is assigned the value 2, and 76 is assigned the value 3. Note that 3 is not skipped here, even though 45 is duplicate data.

Unique Sequential Ranking (UNIQUE)

• Definition: Each value is assigned a unique ranking value, ensuring that no duplicate rankings exist in the data. Even if duplicate values exist, the ranking value will continue to increase.

• Scenario: suitable for ranking systems that require each value in the data to correspond to a unique ranking and do not allow duplicate values For example, in certain analyses, all items must correspond to unique rankings. Or in deduplication analyses, you can use this ranking method to avoid data confusion. For example, in financial investment return rate analysis, each investment plan should correspond to a unique ranking. Even if the return rates of the two investment plans are the same, the system will give them different rankings.

• Example: For the data 23, 45, 45, and 76, the ranking values are 1, 2, 3, and 4. Each unique value is assigned a unique ranking value that gradually increases from 1.

Percentile Ranking (PERCENTILE)

• Definition: The data is assigned ranking values based on their percentage positions in the entire dataset. Proportion values between 0 and 1 are generated, representing the relative position of the data in the set.

• Scenario: suitable for evaluating data distribution by standardizing or normalizing data This ranking method is very common in statistical analysis, especially when you describe the relative position of data, such as determining which percentile a data value corresponds to. This ranking method is widely used in big data analysis, statistical distribution analysis, and social science research.

• Example: For the data 23, 45, 45, and 76, the ranking values are 0, 0.33, 0.33, and 1. This means that 23 ranks at the bottom (0%), 45 ranks in the middle (33%), and 76 ranks at the top (100%).

If you rank the scores of the students in the class, the ranking results of different ranking modes are shown in the following figure.

• Common Ranking: RANK_AGG([],[SUM_AGG(Score),"desc"],"COMMON")

• Dense Ranking: RANK_AGG([],[SUM_AGG(Score),"desc"],"DENSE")

• Unique Sequential Ranking: RANK_AGG([],[SUM_AGG(Score),"desc"],"UNIQUE")

• Percentile Ranking: RANK_AGG([],[SUM_AGG(Score),"desc"],"PERCENTILE")

• For details, see WINDOW Function Overview - Notes.

• RANK_AGG functions directly output aggregation fields, which is not supported in self-service datasets. In this case, RANK_AGG functions need to be used together with DEF functions to output detailed-level fields.

For details, see Dense Ranking.

Creating a Cross Table

Analyze sales of different Subcategories under each category in each area, and set sales to a numerical format, as shown in the following figure.

Calculating Sales Rankings in Different Areas

Group sales by category and Subcategory, and rank the data in the descending order, as shown in the following figure.

Formula: RANK_AGG([category,Subcategory],[SUM_AGG(sales),"desc"])

Drag the field into the cross table to obtain the ranking, as shown in the following figure.