I. Overview
1) A ranking is often used to indicate the relative position of a piece of data in a set of data, for instance:
The ranking of a salesperson nationwide by sales volume.
The ranking of a salesperson in his/her region by sales volume.
2) This article introduces how to calculate a ranking through hierarchical coordinates.
II. Steps
1. Create a new dataset [ds1]
1) Create a new DB Query.
2) Input the following SQL function:
SELECT Region, Salesperson, SUM(Sales_Volume) AS SalesFROM Sales_VolumeGROUP BY Region, Salesperson
2. Design the report body
1) Type headers.
2) Drag data columns in [ds1] into the cell range B3:D3.
3) Preview.
3. Calculate the ranking
1) Input the following formula in E3: COUNT(D3[!0]{B3 = $B3 && D3 > $D3}) + 1.
Formula segment | Meaning |
D3[!0]{B3 = $B3 && D3 > $D3} | Fetch sales volumes higher than that of the current salesperson in the region where the salesperson is located. |
COUNT(D3[!0]{B3 = $B3 && D3 > $D3}) | Calculate how many pieces of data on sales volumes higher than that of the current salesperson in this region. If the current salesperson has the highest sales volume in the region, then the result is 0. |
COUNT(D3[!0]{B3 = $B3 && D3 > $D3}) + 1 | Add 1 to the result obtained in the previous step to get the ranking of the current salesperson in the region by sales volume. |
2) Input the following formula in F3: COUNT(D3[!0]{D3 > $D3}) + 1
Formula segment | Meaning |
D3[!0]{D3 > $D3} | Fetch sales volumes higher than that of the current salesperson nationwide. |
COUNT(D3[!0]{D3 > $D3}) | Calculate how many pieces of data on sales volumes higher than that of the current salesperson nationwide. If the current salesperson has the highest sales volume nationwide, than the result is 0. |
COUNT(D3[!0]{D3 > $D3}) + 1 | Add 1 to the result obtained in the previous step to get the ranking of the current salesperson nationwide by sales volume. |
Previous：Level-by-Level Accumulation
