topic Re: What is the exactly calculation of the fractile function? in QlikView

What is the exactly calculation of the fractile function?

Thu, 28 Apr 2016 13:50:28 GMT

Hi everybody! I am crazy with the calculation of the fractile function.

I have this table:

vendedor:

LOAD * INLINE [

vendedor, venta

1, 77

2, 79

3, 90

4, 85

5, 66

6, 100

7,88

8,97

9,100

10,87

11,90

12,92

];

when I do in object text the calculation: =fractile( total venta , 0.3) return the value: 85.6

BUT... what is the exactly calculation of the fractile function?

Because I am not statistical so I had to learn how function the fractile function and in this video: https://www.youtube.com/watch?v=PDzLETjOlL0

the result is 84.4, different of 85.6 (value returned by Qlikview)

who is wrong? what happens?

Thank you in advance !!!

Re: What is the exactly calculation of the fractile function?

MK_QSL — Thu, 28 Apr 2016 13:51:56 GMT

Fractile() mystery revealed

What exactly a Fractile function does?

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Thu, 28 Apr 2016 13:53:15 GMT

In a text box object, it won't matter if you use TOTAL or not, it would give the same number. But Fractile(venta, 0.3) is the 30th percentile of the data within venta. Where does this 84.4 numbers come from?

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Thu, 28 Apr 2016 14:02:21 GMT

Manish what is the mystery here? Not sure I understand the issue. Can you elaborate the finding from those two links?

Re: What is the exactly calculation of the fractile function?

MK_QSL — Thu, 28 Apr 2016 14:05:49 GMT

Fractile works for values by Ascending order..

vendedor	venta
5	66
1	77
2	79
4	85
10	87
7	88
3	90
11	90
12	92
8	97
6	100
9	100

=fractile( total venta , 0.3)

1) You have total 12 Values so 12 * 0.3 = 3.6

So we need at least 3 values from result...

In fractile we need to add one in this... 3.6 + 1 = 4.6

So we need

=4th Value + (Difference Between 5th and 4th Value)*30% share

=85 + (87-85)*30%

=85 + 0.6

=85.6

Re: What is the exactly calculation of the fractile function?

Thu, 28 Apr 2016 14:08:48 GMT

Hi Sunny T, in this video explains how works the fractile function with the same example that I use

STAT 344 &350 Fractile vs Johnson's Method for calculating Percentiles Quartiles.mp4 - YouTube

Re: What is the exactly calculation of the fractile function?

MK_QSL — Thu, 28 Apr 2016 14:09:41 GMT

Difference is Percentile works like (12+1)*30% = 3.9

=3rd Value + 0.9(4th - 3rd value)

=79 + 0.9*(85-79)

=79 + 5.4

=84.4

Re: What is the exactly calculation of the fractile function?

Thu, 28 Apr 2016 18:25:17 GMT

Hi Manish, your result it's okay.. BUT! try this.. I can't end to understand..

I want to know the porcentage of 70%

Therefore in QV I do =fractile( total venta , 0.7) = 91.4

But if I do the calculation the result is different.

I do:

12 x 0.7 =8.4

8.4 + 1 = 9.4 I use the 9th value

9th + (10th - 9th) x porcent

92 + (97 - 92 ) * 0.7 = 95.5

And 95.5 is different to 91.4 (QV result)

The same happens when I do the calculation with 0.25%.

Thanks for your help, I am really confuse with this issue.

Re: What is the exactly calculation of the fractile function?

MK_QSL — Fri, 29 Apr 2016 06:28:15 GMT

I will work on this and come back to you soon.

Re: What is the exactly calculation of the fractile function?

Fri, 29 Apr 2016 12:34:28 GMT

Ok, thank you so much!

I note different behaviors between porcentage with one or two decimals. for example the calculation for 0.4 is different of 0.45, because when I do the calculation of 0.4 the result is the same that QV shows me, but when I do the same calculation with 0.45 the result that QV gives me is different that I have it.

Other thing that I noted is the position of the elements that I must to take when the porcentage are less of 0.5 or more of 0.5

Fuck!

And other issue that I noted is that I took the same values of the document: 'What exatly a fractile function does? with the same porcentages: 0.25 ; 0.5 and 0.75 and the values that QV shows me are different of the results showed in the document, so crazy!

Re: What is the exactly calculation of the fractile function?

MK_QSL — Fri, 29 Apr 2016 12:39:07 GMT

I will search on this and come back to you next week... I am also puzzled... so would definitely work on this.

Re: What is the exactly calculation of the fractile function?

Fri, 29 Apr 2016 12:44:42 GMT

Okey, thank you!!!

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Sun, 01 May 2016 02:35:12 GMT

Attached the logic in the Doc file with the qvw for fractile from 5% till 95% in the increments of 5%

Fractile X% = (N * X%) + (1-X%)

Where N = number of observations in your database (12 in your case)

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Sun, 01 May 2016 06:06:20 GMT

Adding the Excel as Document's calculations might look a little messy.

Re: What is the exactly calculation of the fractile function?

Mon, 02 May 2016 13:21:06 GMT

Thank you Sunny T !

I apreciate your work really, such in this topic like in the community.

Thank you so much again!

Regards

Agustin

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Mon, 02 May 2016 13:32:46 GMT

I am glad I was able to help. My next goal is to fix this document -> Fractile() mystery revealed‌ because the OP might have made a small mistake which might continue to confuse people in the future. But to give the credit to the person who posted it, I wouldn't have got anywhere close in determining what is going on without reverse engineering/play around with his document.

Best,

Sunny

Re: What is the exactly calculation of the fractile function?

MK_QSL — Tue, 03 May 2016 08:08:17 GMT

Great work Sunny... !!

Re: What is the exactly calculation of the fractile function?

sunny_talwar — Tue, 03 May 2016 08:10:53 GMT

Thanks Manish‌

Re: What is the exactly calculation of the fractile function?

Digvijay_Singh — Mon, 11 Dec 2017 15:38:18 GMT

Thanks a lot Sunny! I also needed this for Sense Box Plot verification.!