"PthPercentile" function in Crystal Reports

[Former Member]

I am currently using Crystal Reports to calculate 75th percentile of a data set and was asked to provide the percentile methodology/formula/algorithm being used by Crystal Reports (as there are different methods).

Others have noted reasons for differences in the percentile results between Crystal and other tools (e.g. http://www.tek-tips.com/viewthread.cfm?qid=1270864 ), so I am hoping that SAP official documentation exists for this topic that I can reference.

I have searched the SAP community Q's and A's and Crystal documentation but cannot find this information. Any direction/assistance is greatly appreciated. Thank You in advance.

[vitaly_izmaylov · Accepted solution]

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.

[Former Member · Accepted solution]

Thank You, Much appreciated !!!