3 Replies Latest reply: Aug 13, 2012 11:36 PM by Pablo Labbe RSS

    Fractile not working as expected

    Pablo Labbe

      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
          Stefan Wühl

          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
            Henric Cronström

            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:

            histogram.jpg

            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
              Pablo Labbe

              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