Fractile not working as expected

[pablolabbe]

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

[swuehl]

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

[hic]

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

[pablolabbe]

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