3 Replies Latest reply: Feb 25, 2014 9:34 AM by Fabrice Aunez RSS

    Top 10 % of Sales, top n percent

      Hey Guys,

       

      I need some help with getting top 10% of Sales to

      CustomerSales
      A3500
      B400
      C246
      D98
      E356
      F757
      G1300
      H5900
      I2545
      J123
      K321

       

      I need to show the list of names in Textbox of top 10% sales (Rank will give me top ten rows, I;m looking for a way to get top 10%)

       

      Sales%SalesAccmulate%Accumulate
      H590037.95%590037.95%
      A350022.51%940060.47%
      I254516.37%1194576.84%
      G13008.36%1324585.20%
      F7574.87%1400290.07%
      B4002.57%1440292.64%
      E3562.29%1475894.93%
      K3212.06%1507997.00%
      C2461.58%1532598.58%
      J1230.79%1544899.37%
      D980.63%15546100.00%
      15546100%

       

      So in this case if I am looking for top 10% then I should get the first row which is customer H

      If i am looking for 50%, then I should get Customer H and A

       

      I need to get the list of names in textbox  as i will need to do further search string.

       

      Thank you in advance for your kind assistance.

       

      Krishna

        • Re: Top 10 % of Sales, top n percent

          Krishna,

           

          use the fractile() and concat() function.

          Sth like:

           

          =concat({<ID= { "=(Customer > fractile(total Sales, 0.9))"} >} ID, ';')

          Because we create a boolean expression in the set analysis, you need a 3d field (here id).

          0.9 to get the top 10%, 0.8 to get the top 20% etc.

           

          Fabrice

            • Re: Top 10 % of Sales, top n percent

              Hi Aunez,

               

              I tried but it still not working.

               

              Let me explain my problem a little more.

               

              CustomerSalesSales %
              A12331.10%
              B23231.20%
              C45452.30%
              D23231.32%
              E34341.90%
              F656

              4%

               

              the calculation process must first sort by top sales

               

              CustomerSalesSales%Accumulate Sales %
              C45452.30%2.30%
              E34341.90%4.20%<--
              D25871.32%5.52%
              B23231.20%6.72%
              A12331.10%7.82%
              F6560.40%8.22%

               

              how can i get concat names of those customers within 5% of total sales.

               

              Thank you in advance for your help