Skip to Content
avatar image
Former Member

"PthPercentile" function in Crystal Reports

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 17, 2017 at 04:29 PM

    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
    10|10000 characters needed characters exceeded