topic Re: Need Help in QlikView

Need Help

Nagaraju_KCS — Thu, 25 Jun 2015 12:40:37 GMT

Hi Community,

I am trying Fractile Function..

Fractile:

LOAD * Inline [

FracID

];

I have one textbox Fractile(FractID, .25) it returns 34

Fractile() mystery revealed

I have seen this document,

but if i am calculate like that, i didn't get 34

Please anyone explain...!!!!

Re: Need Help

morganaaron — Thu, 25 Jun 2015 12:58:34 GMT

Hi QV

Unsure what you're saying here - I used the fractile function on that dataset, and also tested using the percentile function in excel, and both returned 34...

How did you calculate it that you didn't get 34? You may be doing a different calculation?

Re: Need Help

Nagaraju_KCS — Thu, 25 Jun 2015 13:08:29 GMT

HI Morgan,

I have total 9 values in the listbox.

Fractile 25th Calculation, here the result 25% of 9 means ==> 0.25*9 = 2.25..

Means 2nd value from the list + .25 share between 2nd and 3rd.value.

2nd value 12 and 3rd value 34. difference is 22.

0.25 share of 22 =0.25*22=5.5 (Difference value = 5.5)

Final result 2nd value (12)+5.5= 17.5

is this correct calculation ????

Re: Need Help

morganaaron — Thu, 25 Jun 2015 13:34:06 GMT

No, sorry - I didn't see the word document. The document itself as far as I understand is incorrect, the way in which I believe the fractile calculation works is the same as the percentile function in excel, in that it first calculates a rank from the data, which is then split into an integer and decimal part. It's worth noting this differs depending on if you're calculating the fractile inclusively or exclusively, but you can do more reading about that if need be - here it's assumed it's inclusive.

The formula for the rank is n = P/100(N-1) + 1

Here, N = 9, P/100 = 0.25 (as you're trying to figure out).

So n = 0.25(8) + 1 = 3. Therefore it's taking the third value to work out the 25th percentile, which is 34.

I can link to further reading material if need be, but just note there are various ways to calculate percentiles/fractiles/quartiles, depending on your distribution and choice of method. Here, that's the method I think is being used!

Re: Need Help

morganaaron — Thu, 25 Jun 2015 13:45:23 GMT

As a further point, unless how fractile is calculated has changed, when I used his dataset and calculate the 25th percentile or fractile using 0.25, it returns 27 and not 27.75 - so perhaps the function changed or there was an error in his calculations!

Re: Need Help

Nagaraju_KCS — Thu, 25 Jun 2015 13:56:42 GMT

Really it's good for me..

but i have one doubt..

If i have 26th of percentile

n = P/100(N-1) + 1

Here, N = 9, P/100 = 0.26

So n = 0.26(8) + 1 = 3.08.

then what will return ???

Re: Need Help

morganaaron — Thu, 25 Jun 2015 14:03:13 GMT

Here it then interpolates for the decimal component.

So the integer remains the same (i.e. your 3rd value is 34), but then it interpolates the 0.08 component, and it does that in pretty much the way in which the document suggests.

Its calculated by your component (d) by the difference between the two values it dissects.

so 0.08 * (45-34) = 0.08 * 11 which gives you 0.88. Add the components together and your value is 34.88 (which is what Qlikview also returns).

Re: Need Help

Nagaraju_KCS — Thu, 25 Jun 2015 14:13:10 GMT

Thanks for Quick Response...