Issues with Fractile Function in Qlikview

[#1]

Greetings Everyone

I am trying to use the Fractile function.

Please refer to the following video from YouTube on Percentile Function issues with Microsoft Excel

http://www.youtube.com/watch?v=13Ahbn9o-5I

So I am using the exact Data

CPA_SCORES
7
17
26
27
30
38
39
42
43
45
54
54
59
97

If I create a Text Object and assign expression =Fractile(CPA_SCORES,0.9)  I am getting the answer 57.5 instead of 78(as per the new calculation method)

Microsoft does not recommend Using Percentile function instead they recommend Percentile.INC function.

Since we see the same issue in QlikeView, how to solve the problem?

Thank you

[swuehl · Accepted solution]

sairam06,

I am sorry, I can't really see the issues mentioned in the referenced video.

Just for clarification, the QV fractile() function returns same results as the old Excel function percentile(), right?

Now, MS introduced another function, percentile.exc. To make thinks more consistent, they depricated the old percentile function, but created another new function percentile.inc, that works exactely like the old function, like an alias, right?

The old percentile function, percentile.inc as well ass QV return 57.5 as 90% percentile with your sample data.

The author said, this value is ridiculous, but didn't say, why so. In fact, I can't see anything ridiculous. It's just an interpolated value that gives you a 90% threshold. The next lower value in your data is 54, and 86% of your values are lower or equal that value. The next higher value is 59, and 93% of your values are lower or equal 59.

So, a value inbetween 54 and 59 is not ridiculous, it makes sense in some way.

It would also makes sense to me to either return 54 or 59, i.e. values from your available set of values, not an interpolated value.

But why would I am interested in 78? Given the fact that already 93% of your values are already <= 59?

So, in summary, I don't see an issue with the QV fractile function, since its behaviour is comparable to Excel percentile.inc / percentile and it returns something meaningful to me.

The Excel function percentile.exc is calculating something different and there is no direct counterpart in QV (like for many other functions, too). The returned value may be useful / meaningful, too, but I don't see your issue here.

[in fact, it shows other issues, given the nature of its calculation it won't return anything for some percentiles, like 95% with your sample data.]

Regards,

Stefan

[#3]

Hello Stefen

Thank you and appreciate your detailed response. I did googling.

Here is the answer from  Mr. Middleton

Source:  http://answers.microsoft.com/en-us/office/forum/office_2010-excel/percentileexce-versus-percentilinc...

What is the difference between the formula which uses PERCENTILE.EXC versus PERCENTILE.INC?  I understand what they are, but I do not understand their significance.  How would a user choose between them?  Why?

Referring to the Wikipedia entry for Percentile, http://en.wikipedia.org/wiki/Percentile, "There is no standard definition of percentile, however all definitions yield similar results when the number of observations is very large."

Both PERCENTILE.EXC and PERCENTILE.INC (same as the original PERCENTILE) first rank the N values of the data Array argument from 1 (lowest value) to N (highest), then determine the possibly-non-integer calculated rank for the specified percentage argument K (a decimal number between 0.00 and 1.00), and finally use linear interpolation between the closest integer-rank values of the data array.

PERCENTILE.EXC and PERCENTILE.INC differ only in the way the possibly-non-integer rank is calculated.

For PERCENTILE.INC (and PERCENTILE) the calculated rank is K*(N-1)+1.

For PERCENTILE.EXC the calculated rank is K*(N+1).

(For more details about the calculated rank, refer to the "Alternative methods" section of the Wikipedia entry.)

The functions then use linear interpolation to determine the value of the percentile function.

(For more details about the interpolation, refer to the "Linear interpolation between closest ranks" section of the Wikipedia entry.)

For a very small data set you might choose to use PERCENTILE.INC (or PERCENTILE) instead of PERCENTILE.EXC because PERCENTILE.EXC returns the #NUM! error value for values of K <= 1/(N+1) and for values of K >= N/(N+1).

-  Mike Middleton, www.MikeMiddleton.com, www.TreePlan.com

So as of now QV do not support PERCENTILE.EXC. I think my current usage I can use what is supported by QV.

Once again Thank you