topic Re: Fractile not working as expected in QlikView

Fractile not working as expected

pablolabbe — Sun, 12 Aug 2012 03:13:41 GMT

Hi All,

The table has 2 columns : customer name and sales value with 312.844 rows.

I need to get the bottom value on 3 tiers: 25 % for the first, 50 % for the second and 75 % for the third.

I'm trying to use the factile function, but I think it´s not working as expected.

When I select a hundred of customers, it works. But when I clear the selection , the values returned doesn´t make sense.

the expression are simple:

fractile ( sales, 0.75), fractile ( sales, 0.50) , fractile ( sales, 0.25)

There are any know issues with fractile and large datasets ?

I do the tests with QV10 sr4 and QV11 sr4.

The QVW is attached with the sample data and a test chart with the expressions above.

Best Regards,

Pablo

Re: Fractile not working as expected

swuehl — Sun, 12 Aug 2012 12:42:23 GMT

I can't see anything wrong with your fractile number (looking at the complete data set).

What do you expect to see?

I compared the outcome for example for the 2-quantile with the median(VENDA) as well as with

=min(aggr(if(rank(-sum(VENDA),4)=floor(312844/2),sum(VENDA)),CLIENTE))

and got returned 44,4 (as with your table expression).

Also the other two values seemed reasonable to me.

I noticed that you are showing a cumulative relative (percentage) of the sum(VENDA), though I believe you shouldn't look at e.g. the 50% threshold VENDA value while looking for the 2-quantile / median, these are different metrics.

Regards,

Stefan

Re: Fractile not working as expected

hic — Sun, 12 Aug 2012 15:06:44 GMT

I agree with Stefan. I cannot find anything wrong with the fractile calculation.

I made a histogram to analyze the data, and it shows that 0, 44.4 and 165.7 are the fractiles you search for. I hard-coded these numbers in a calculated dimension and got this bar chart:

In it you can see that very few values are below 0, roughly 27% are exactly 0, etc. So the fractiles are correct.

HIC

Re: Fractile not working as expected

pablolabbe — Tue, 14 Aug 2012 03:36:06 GMT

Stefan and Henric,

Thank you for your answers and correct me on the usage of this function. My mistake .

I try to use this function to get the mininum value of sales on each quartile. I´ve created the cumulative % of Sales to check if the resulting value of the fractile column was correct.

Now I think it´s worth to use some sort of combination of rank , min and aggr functions to get what I need.

Best Regards,

Pablo Labbe Ibaceta