The following equation can be used to show how the Percentile function calculates the numeric value in MicroStrategy Engine:
[(P*n) - rank1] / [rank2 - rank1] = [x - (metric value @ rank1)] / [(metric value @ rank2) - (metric value @ rank1)]
[(P*n) - rank1] / [rank2 - rank1] * [(metric value @ rank2) - (metric value @ rank1)] = x - (metric value @ rank1)
x = [(P*n) - rank1] / [rank2 - rank1] * [(metric value @ rank2) - (metric value @ rank1)] + (metric value @ rank1)
Rank1 and Rank2 are determined by first assigning percentage values to each rank according to the linear formula Rank(i) / n, where the ranks begin with 0 and n is the highest rank. For instance, if there are five metric values, they would be ranked as 0, 1, 2, 3, and 4, and the assigned percentage values would be (0..4) / 4, or 0, 0.25, 0.5, 0.75 and 1.P, the percentage specified in the percentile function, must fall between the percentages assigned to Rank1 and Rank2. That is, if P is 40% or 0.4, the assigned percentages surrounding that value are 0.25 and 0.5. Thus Rank1 would be 1 and Rank2 would be 2. P*n would be 0.4 x 4, or 1.6.
The remainder of the formula performs a simple linear interpolation, in which the proportion of the input percentage to its assigned neighbors is the same as the proportion of the output value to its neighboring metric values.
If P = 0.4, (P - 0.25) / (0.5 - 0.25) = 3/5, so x should be 3/5 of the way between 30 and 60, or 48. Thus 40% of the metric values would be expected to be less than 48.
Create a second metric that is the Rank of the first metric -1. The reason the metric needs to subtract one is that the Percentile function starts its calculation from zero and not from one in regards to the ranking. The second metric should have the following rank parameters. Also, make sure the metric format is set to fixed with zero decimal places.
On the Revenue fact, determine which table will be referenced, either by running a small report or looking at the logical table size of each source table in the fact definition.
In this example the smallest table is YR_CATEGORY_SLS. Since there are two keys in this table, the template should contain at least these two keys. The following template contains metrics #1 and #2 above and the attributes 'Year' and 'Category'.
The report returns the following data when executed.
This data can be sorted by rank by going to Data > Advanced sorting and adding the metric to the sort properties.
|
| Metrics | Sum of Dollar Sales | Rank of Dollar Sales by Year | Percentage |
Year | Category |
|
|
|
|
| Kid's Korner |
| 2,559 | 0 | 0 |
| Travel |
| 4,497 | 1 | 0.166666667 |
| Food |
| 5,300 | 2 | 0.333333333 |
1997 | Health & Beauty |
| 6,042 | 3 | 0.5 |
| Electronics |
| 10,616 | 4 | 0.666666667 |
| Gifts |
| 16,315 | 5 | 0.833333333 |
| Household |
| 38,383 | 6 | 1 |
| Kid's Korner |
| 2,943 | 0 | 0 |
| Travel |
| 4,792 | 1 | 0.166666667 |
| Food |
| 5,638 | 2 | 0.333333333 |
1998 | Health & Beauty |
| 5,665 | 3 | 0.5 |
| Gifts |
| 20,047 | 4 | 0.666666667 |
| Electronics |
| 29,299 | 5 | 0.833333333 |
| Household |
| 50,391 | 6 | 1 |
Create a new report with the 40th percentile metric and the attribute 'Year'.
This report returns the following results:
[(P*n) - rank 1] / [rank2 - rank 1] = [x - (numeric value @ rank1)] / [(numeric value @ rank2) - (numeric value @ rank1)]
[(.4*6) - 2] / [3- 2] = [x - (5300)] / [6042 - 5300]
0.4 / 1 = (x - 5300) / 742
0.4 * 742 = x - 5300
296.8 = x - 5300
x = 5596.8
Note for data with tied ranks:
Consider the example used above. If both the 'Food' and the 'Health & Beauty' categories for the year 1998 have the same value for 'Sum of Dollar Sales', the rank displayed for category in the report shown in step 6. would be the same '2' and the rank for category 'Gifts' would be '4'.
However when calculating the percentile value, the rank assigned to 'Food' would be '2' and the rank assigned to 'Health & Beauty' would be '3' even though the metric values are the same.
