HRank() evaluates the expression, and compares the result with the result of the other columns containing the current row segment of a pivot table. The function then returns the ranking of the current column within the segment.
Syntax:
HRank([ total ] expression [ , mode [, format ] ])
Return data type: dual
Arguments:
- expression: The expression or field containing the data to be measured.
- mode: Specifies the number representation of the function result.
- format: Specifies the text representation of the function result.
- TOTAL: If the word TOTAL occurs before the function arguments, the calculation is made over all possible values given the current selections, and not just those that pertain to the current dimensional value, that is, it disregards the chart dimensions. The TOTAL qualifier may be followed by a list of one or more field names within angle brackets <fld>. These field names should be a subset of the chart dimension variables.
If the pivot table is one-dimensional or if the expression is preceded by the total qualifier, the current row segment is always equal to the entire row. If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter-field sort order.
The ranking is returned as a dual value, which in the case when each column has a unique ranking will be an integer between 1 and the number of columns in the current row segment.
In the case where several columns share the same ranking, the text and number representation can be controlled with the mode and format arguments.
The second argument, mode, specifies the number representation of the function result:
| Value | Description |
|---|---|
| 0 (default) | If all ranks within the sharing group fall on the low side of the middle
value of the entire ranking, all columns get the lowest rank within the sharing
group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all columns get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment. |
| 1 | Lowest rank on all columns in the group. |
| 2 | Average rank on all columns in the group. |
| 3 | Highest rank on all columns in the group. |
| 4 | Lowest rank on first column, then incremented by one for each column in the group. |
The third argument, format, specifies the text representation of the function result:
| Value | Description |
|---|---|
|
0 (default) |
Low value&' - '&high value on all columns in the group (for example 3 - 4). |
| 1 | Low value on all columns in the group. |
| 2 | Low value on first column, blank on the following columns in the group. |
The order of columns for mode 4 and format 2 is determined by the sort order of the chart dimensions.
Examples:
HRank( sum( Sales ))
HRank( sum( Sales ), 2 )
HRank( sum( Sales ), 0, 1 )
