Crytal Percentile Calculation

[HRoarke]

Hello -

I am finding there to be unacceptable discrepancies between percentiles calculated via CR10 running totals and percentiles calculated on the same data set using, for example, Excel. Does anyone have any info on why Crystal comes up with a different result for percentiles? The discrepancies appear to get larger as you get to large or small percentiles, e.g. 5th/95th percentile discrepances are much larger than 50th percentile discrepancies.

I appreciate any information anyone can give regarding the algorithm used by Crystal, or any solutions you may have found to resolve what I believe to be bad math by Crystal.

 

[synapsevampire]

Post an example percentage for each product, including some sample data.

I remember going through this exercise a few years ago, and there was a difference, however it was discovered to be inaccurate in Excel, or possibly even subject to interpretation.

-k

 

[IdoMillet]

Why a running rather than a regular total? Percentiles make more sense in the context of a regular total.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[HRoarke]

Synapsevampire -

I may have just recreated your exercise of a few years ago. To get Crystal's percentile result, there are two differences between CR's algorithm and Excel's:

1 - Crystal does NOT interpolate bewteen low and high values where the rank is not a whole number. For example,if the target rank position for percentile P is 6.25, the interpolation of the Pth percentile **should** be [datapoint 6] + ([datapoint 7]-[datapoint 6] * 0.25). Crystal, however, takes the simple average (this seems to me like nothing more than laziness on the part of the CR development team....)
2 - when determining the rank position for percentile P, Excel uses the algorithm [1 + P * (count_of_datapoints - 1)]. Crystal, however, uses [P * (count_of_datapoints + 1)]. Not sure about the whys and wherefores of this one (the NIST page I reference for stats info is down at the moment so I can't dig into it any more).

Idomillet - I designed the report with a running total 'cause that's what was needed to meet the requirements of the report I was writing, but thanks for asking.

Hope this info helps someone in the future.....

"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first