Use this dialog to create a percent rank calculation. A percent rank calculation calculates the relative position of a value in a group of values, and expresses the result as a percentage. For example, you might want to find out which products are in the top 25% and top 50% of best selling products. In other words, if you calculate the percent rank for sales totals for 15 stores (highest value ranked 1):
the store ranked 4th has the percent rank value of 25%
the store ranked 8th has the percent rank value of 50%.
You can therefore find the top 25% and 50% of the best performing stores.
Note: Percent Rank analysis is similar to cumulative distribution.
For more information, see:
"What are analytic functions?"
"How to create a calculation using an analytic function template"
Use this drop down list to choose the item for which you want to calculate the percentage rank. For example, to calculate the percent rank of stores, 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 highest sales to have the highest ranked list positions. In other words:
if you choose Highest Value Ranked 1, the highest ranked item has the percent rank value of 0%
if you choose Highest Value Ranked 1, the lowest ranked item has the percent rank value of 100%
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 percent 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.
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".
To calculate just the ranked list position of items, see "Rank dialog".
You can find the median value in a set of values by looking at the value with 50% (or nearest 50%) as the percent rank value.
Discoverer uses an underlying PERCENT_RANK() function for this calculation.
Percent rank is calculated as the rank of each row minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition). The range of values returned is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK value of 0.
For example, a value ranked 3 in a group of 5 values has the percent rank of 0.5 (that is, 3 minus 1 divided by 5 minus 1, or 2 divided by 4, which gives 0.5 or 50%).