4 Replies Latest reply: Sep 9, 2013 4:40 AM by Ali Hijazi RSS

    80/20  graph

    Stefano Peroni

      Dear all,

      I've to list the first customers which represent the 80% of the total turnover.

      I'm working with the Qlikview 10 Personal Edition.

       

      Table1

       

      Customer     Turnover

      A                    4000

      B                    5000

      C                    3000

      D                    1000

      E                    1000

      F                    1500

      G                     500

       

      In this case: totale turnover 16000 euro

      80%  =    12800 euro

       

      So I've to list the first four customers.

      Does anyone have the solution ? I'm really confused.

       

      Thanks for your help

       

      Stefan

        • Re: 80/20  graph
          Roberto Postma

          In Qlikview 11 there is a possibility to do "pareto select". I have no idea if this also exists in QV10, so you could check that.

           

          What it does is select those customers that contribute x percent (eg 80%) to your turnover (or some other field you define.

           

          Steps to create this

          -1: add new sheet object > button

          - 2: in its properties, tabpage actions, choose 'pareto select'

          - 3: as field place Customers, expression sum(Turnover) and percentage 80 (in all fields: don't put an = sign before it)

          - 4: click OK and give the button a name on tab general first

          - 5 hit the button. Note that it selects customers a, b, c, f in this case, which means 13.500 is the sum of the turnover because those customers (raked from high to low Turnover) were responsible for the 80% of the turnover.

           

          Hope this helps,.

          I made the QVW to check this functionality for myself. I just uploaded it for anyone who is interested. I can imagine you cannot open it (since you use personal edition).

           

          Hope it helps (and I hope it is available too in QV 10)

           

          PS: I see you marked other customers in bold... The Qlikview-pareto-select takes those customers that contributed 80% of the turnover in this case sorted by Turnover descending. Hence the selection of customers customers a, b, c and f

            • Re: 80/20  graph
              Sander Janssen

              Hi, in QV10 this is also possible. In QV11 there is a new and very easy way to achieve this. Create a table (Pivot) and then you go to the Tab Dimension Limits (new in 11). Select "Show only values that accumulate to: 80% relative to the total; using largest values. Also check Include Boundary Values (i.e. F to be included). Very handy, maybe another reason to switch to 11. Unless you have to stay on 10 for some reason. Regards, Sander

            • Re: 80/20  graph
              Sebastian Pereira

              If you don't want to use this QV funciontality, you must:

               

              Dimention: Customer

              Sort: Expression sum(Turnover) desc

              Expression:

              If(rangesum(above(Sum(Turnover),0,rowno()))/sum(total Turnover)<0.8, Sum(Turnover))

               

              0.8 is the limit. If you want to show 60/40, change it by 0.6.