topic Re: Fractile function in QlikView

Fractile function

Mon, 03 Dec 2012 12:02:56 GMT

Hi everyone,

I have a list of customers with profits associated. I want to use the fractile function. I was thinking that the fractile function take the 10 first % of my clients and calculated the profit. but in fact, it takes the 10% of profits.

How can I have this :

0-10% of best clients profit | number of customers (and each line contains the same number of customers)

10-20% | 1 759

20-30% | 1 759

30-40% | 1 759

...

Thanks!

Re: Fractile function

swuehl — Mon, 03 Dec 2012 12:38:10 GMT

Sorry, I haven't got what you are trying to accomplish.

If you are in doubt, what a fractile calculation does, please check:

http://en.wikipedia.org/wiki/Fractile

or start with the well known special case of a median:

http://en.wikipedia.org/wiki/Median

If you want to get all customers per line:

=count(TOTAL customer)

=count(TOTAL customer) / 10

to get your required 1759 (i.e. count of TOTAL divided by number of your classes)

Re: Fractile function

Mon, 03 Dec 2012 12:44:34 GMT

thanks for your answer

I was using the count(customer) to check the result of fractile function.

I was thinking that if I use fractile(), each lines will have the same number of customers. But it's not.

"In descriptive statistics, any of the nine values that divide the sorted data into ten equal parts"

And it's false if i used the fractile function, there is not ten equal parts

Re: Fractile function

Mon, 03 Dec 2012 13:31:01 GMT

Any help please ?

Is it clear?

Re: Fractile function

swuehl — Mon, 03 Dec 2012 13:39:02 GMT

I think it's quite clear. I see what you are talking about.

Please note that the fractile function itself is not dividing your data set into equal parts, it's just returning the threshold value that divides your data set into equal parts.

The issue arises if you have clustered data (like in your case, your data is clustered around the integer values or values with a single decimal, create a histogram or a list box with frequency count to see what I mean).

If a fractile calculations returns one of the clustered values, your chart will count all of these records even if only part of should be counted.

To illustrate:

LOAD *, recno() as Value2, 1 as Value3 INLINE [

Value

];

This creates three different distibutions, try calculating your deciles for all three and check the outcome.

Hope this helps,

Stefan

edit: using rank() in the calculated dimension might be get you a bit closer to what you want to achieve

=aggr(if(rank(sum(Profit),0,4)<0.1*count(total Customer), '90-100%',

if(rank(sum(Profit),4)<0.2*count(total Customer), '80-90%',

if(rank(sum(Profit),4)<0.3*count(total Customer), '70-80%',

if(rank(sum(Profit),4)<0.4*count(total Customer), '60-70%',

if(rank(sum(Profit),4)<0.5*count(total Customer), '50-60%',

if(rank(sum(Profit),4)<0.6*count(total Customer), '40-50%',

if(rank(sum(Profit),4)<0.7*count(total Customer), '30-40%',

if(rank(sum(Profit),4)<0.8*count(total Customer), '20-30%',

if(rank(sum(Profit),4)<0.9*count(total Customer), '10-20%'

, '0-10%'))))))))),Customer)

Re: Fractile function

Mon, 03 Dec 2012 13:50:32 GMT

thanks a lot for your help!

Re: Fractile function

Wed, 12 Dec 2012 08:57:35 GMT

There is an error in the script :

It works with rank(sum(profit), 4, 1)

=aggr(if(rank(sum(Profit),4, 1)<0.1*count(total Customer), '90-100%',

if(rank(sum(Profit),4, 1)<0.2*count(total Customer), '80-90%',

if(rank(sum(Profit),4, 1)<0.3*count(total Customer), '70-80%',

if(rank(sum(Profit),4, 1)<0.4*count(total Customer), '60-70%',

if(rank(sum(Profit),4, 1)<0.5*count(total Customer), '50-60%',

if(rank(sum(Profit),4, 1)<0.6*count(total Customer), '40-50%',

if(rank(sum(Profit),4, 1)<0.7*count(total Customer), '30-40%',

if(rank(sum(Profit),4, 1)<0.8*count(total Customer), '20-30%',

if(rank(sum(Profit),4, 1)<0.9*count(total Customer), '10-20%'

, '0-10%'))))))))),Customer)

thanks a lot !

Re: Fractile function

Rajesh31 — Wed, 07 Dec 2022 17:00:54 GMT

why are you using 4,1 , what does it signifies