1214682 - The PthPercentile function in Crystal Reports

In Crystal Reports, when using a formula with the PthPercentile function the formula output is not as expected if the data set is small (20 elements or less).

Why does the PthPercentile function output incorrect data when used against a small data set?

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

The PthPercentile function does not output incorrect data when used against a small data set. The output may not be as expected because, with a small data set, the output can vary considerably. The function performs the same calculation no matter the size of the data set.

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.

PTHPERCENTILE FUNCTION PERCENTILE PERCENTAGE PER CENTILE PER CENT INCORRECT DATA Crystal Reports PthPercentile function Formula output seems incorrect Crystal functions , c2011414 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 9.0