5 Replies Latest reply: Oct 15, 2015 4:34 AM by bruno bertels RSS

    Top 80%

    Greg Thomas

      I've found lots of stuff on this for QV (surely it can't be that hard to do one of the most common requirements?!).

       

      What I need is an expression for QlikSense that counts the number of clients making up the top 80% so that I can use the 'limit' option (I think). I have been working with RangeSum() & RangeCount() but as soon as you introduce these into a table the sort reverts to Client ascending. e.g. RangeSum( top(sum(Revenue),1,RowNo()) )

        • Re: Top 80%
          Gysbert Wassenaar

          I don't think this is possible unless your clients are loaded in order of revenue total in the script. The aggr function doesn't have an option to sort the virtual table it creates. And the Limit option in Qlik Sense is rather limited and doesn't have a 'Show only values that accumulate to' like Qlikview has. You'll have to do something like explained in this blog post and manually count the clients: Recipe for a Pareto Analysis

          • Re: Top 80%
            bruno bertels

            Hi Graeme

             

            May be somethink like this below may help you

             

            in this table I sum the sales per "conseiller" : (Nbr Vendus PRO)

            I calculate the % of the total sales per rep with a range sum above function ( PARETO LIKE)

             

            then I add a "TEST column that count the number in a cumulative mode of "conseiller" that participate with the 80 % of total sales.

             

            So i see that 48 Conseillers are making 80 % of the sales.

             

            to achieve this a add this formula in the test column :

             

            if(

            RangeSum(Above(

            Count({$<[Resultat Rapport Rdv]={'Vendu'}>}[Resultat Rapport Rdv]),0,RowNo()))

            /

            Count(TOTAL{$<[Resultat Rapport Rdv]={'Vendu'}>}[Resultat Rapport Rdv])<=0.8,

            Rangesum(above(count(distinct(Conseiller)),0,rowno()),null()

            ))

             

            Hope it's help

             

            Bruno

              • Re: Top 80%
                Greg Thomas

                Thx Bruno.

                I'm able to identify the required data with Rangesum(Above... and use an offset of 1 to include the full 80% -

                 

                Where it falls down is trying to implement this for a Qlik Sense pie chart. I planned on working out the correct number of clients and then use the 'Fixed number' option -

                But I can't get it to return any results (i.e. a single value)

                 

                Regards.

                 

                p.s. I'm astounded this is not an option, perhaps someone from Qlik could shed some light!

                  • Re: Top 80%
                    bruno bertels

                    Hi

                     

                    Not sure to be able to help you more. Too newbee in QlikSense.

                     

                    Nethertheless, if you want in your pie chart only the client reaching your 80%'s goal , I think you must use a calculated dimension ( Only the Client that make 80% of sales)

                     

                    I don't know how to achieve that, Sorry

                     

                    May be a Aggr fonction with a range sum above fonction to sort only the usefull client?

                     

                    Bruno

                • Re: Top 80%
                  neetha P

                  Hi,

                   

                  Maybe Try expression:

                   

                  If(RangeSum(Above(Sum(Sales),0,RowNo()))/Sum(TOTAL Sales) <= 0.8,Sum(Sales))