topic Re: Fractile() with few values in QlikView

Fractile() with few values

Anonymous — Thu, 10 Dec 2015 08:51:59 GMT

Hi all,

Although it is often said that they work the same, Fractile() works a little different from the Percentile() function in Excel. I read the article 'Mystery revealed for Fractile() function in Qlikview' (see attachment) and it is now clear to me how the function calculates its outcome from many values.

I still have two questions that I hope you could answer for me:

1. How exactly does Fractile() calculate its value from very few values?

F.e. we have 5 data points a = 3, 5, 6, 9, 16. How is fractile(a, 0.9) calculated?

2. The article 'Mystery revealed for Fractile() function in Qlikview' implicitly suggests in one of its examples that duplicates are removed when fractile() does its calculations. Is this true?

All help is greatly appreciated. Thanks in advance!

Re: Fractile() with few values

swuehl — Fri, 11 Dec 2015 09:12:22 GMT

1. Fractile() works like the Excel Percentile / Percentile.INC function. Or like second variant, C=1, in

Percentile - Wikipedia, the free encyclopedia

which also explains the algorithm pretty good.

2. Duplicates are not removed. Don't get confused looking at a list box.

Re: Fractile() with few values

Anonymous — Fri, 11 Dec 2015 13:29:41 GMT

Hi Swuel,

Thanks for your reaction, but in the document attached in my first post, is clearly shown that fractile() does NOT work the same as percentile() in Excel.

De document shows exactly how the fractile() function calculates its values with larger amounts of data.But I can't find anything about the working of the function with small amounts of data, like in the example in my first post.

Hope to hear from anyone soon. Thanks in advance.

Re: Fractile() with few values

swuehl — Sun, 13 Dec 2015 22:41:10 GMT

Thanks for your reaction, but in the document attached in my first post, is clearly shown that fractile() does NOT work the same as percentile() in Excel.

Sorry, I don't understand what you are talking about.

Just open Excel, copy the 14 ( ! ) values of the Word document into a sheet and use percentile.inc() function with thresholds of 0.25, 0.5, 0.75. You will get 27.75, 40.5 and 51.75.

Where in this document you've attached is any proof that Excel function percentile.inc() / percentile() is different from QV's fractile()?

If you would read the Wikipedia entry referenced above, it will explain very accurate how these fractile values are calculated, regardless of the number of values in the set, it doesn't really matter if you have 5 values as in your original post or hundreds / thousands / millions. The algorithm will be the same.