Rank dialog

Use this dialog to create a rank calculation. A rank calculation calculates the ranked list position of values. For example, you might want to know what are the top ten highest performing stores based on sales.

Hint: To perform top-n analysis, apply a condition to the worksheet to limit the data by rank. For example, you might use a condition where Rank is less than or equal to ten to find the top ten values.

For more information, see:

"What are analytic functions?"

"How to create a calculation using an analytic function template"

"Ranking function examples"

Rank based on

Use this drop down list to choose the item for which you want to calculate the rank. For example, to calculate the relative position of stores based on sales, you might choose Sales SUM.

Use the adjacent drop down list to specify the ranking order. For example, you might choose Highest Value Ranked 1 if you want stores with the higher sales to have the higher rank.

Then rank based on

Use this drop down list to choose an item that is used to determine the rank of values if they have tied ranks (that is, the same rank in the Rank based on field).

For example, to find stores with the highest sales and lowest costs you might choose the following:

  • Sales SUM and Highest Value Ranked 1 in the Rank based on field

  • Costs SUM and Lowest Value Ranked 1 in the Then rank based on field

In other words, if you have two stores with the same Sales SUM value, you might determine the rank by looking at the Costs SUM value to see which store has the lowest costs.

Restart ranking at each change in

Use this list to specify the groups to be used to arrange results into groups (sometimes referred to as partitions).

For example, to calculate a rank of Sales SUM values within year, you might choose Calendar Year. In other words, if Sales SUM values span two years you would have a best performing store for each year.

If you do not specify a group, Discoverer treats all worksheet values as a single group.

Rank Ties

Use these options to specify how you want to calculate the rank of a value following two or more identical values (known as ties).

  • Normal - use non-consecutive ranking numbers when values are equal. For example, if the top five values are equal, assign the rank 1 to each value, and assign the rank 6 to the next ranked value.

  • Dense - use consecutive ranking numbers when values are equal. For example, if the top five values are equal, assign the rank 1 to each value, and assign the rank 2 to the next ranked value.

Calculation

This read-only field displays the underlying formula that you are building as you specify values for the fields above. This formula is updated each time you change one of the values in the fields above. When you click OK, the formula is transferred to the Calculation field in the "New Calculation dialog" or the "Edit Calculation dialog".