There is an old KBA for Crystal Reports:
1214682 - The PthPercentile function in Crystal Reports
and it explains How the PthPercentile is calculated:
How CR calculates the PthPercentile on a List of Values
-------------------------------------------------------
1. The values are sorted in ascending order.
2. Each value in the data set is assigned an index number starting with 1.
3. A calculation finds the index.
N = P / 100 * (k + 1)
Where:
k = total elements, numbers or values in the data set
P = Percentile (This is specified in the formula)
N = Index number in the data set that corresponds to the percentile chosen
4. The formula returns the value associated with the index.
Examples Using the PthPercentile Function on a Small Data Set
-------------------------------------------------------------
1. If N equals an index number the formula will bring back the value associated with that index number.
Given the list of numbers [2, 4, 5, 23], the calculation for the 60th percentile is:
N = 60/100 * (4 + 1)
N = 3
The formula returns the third element, 5.
2. If N is not equal to an index number the formula returns the average of the two values associated with the two indexes that N lies between.
Given the list of numbers [2, 4, 5, 23], the calculation for the 75th percentile is:
N = 75/100 * (4 + 1)
N = 3.75
The formula returns the average of the third and fourth values, (5 + 23)/2 = 14
3. If N is greater than the highest index number the value associated with the highest index number is returned.
Given the list of numbers [2, 4, 5, 23], the calculation for the 90th percentile is:
N = 90/100 * (4 + 1)
N = 4.5
The formula returns the fourth element, 23.
Example Using the PthPercentile Function on a Large Data Set
------------------------------------------------------------
1. In a large data set, the 90th percentile will not necessarily be the maximum:
Given the list of numbers [10, 20, 30, 40, 50, ..., 170, 180, 190, 200], the calculation for the 90th percentile is:
N = 90/100 * (20 + 1) = 18.9
The formula returns the average of the 18th and 19th values, (180 + 190)/2 = 185
In the first three examples you can see that the PthPercentile function does not necessarily output a value that is expected but the value is correct.
Add comment