5 Replies Latest reply: Apr 9, 2012 11:36 PM by Maxim Senin RSS

    ABC ABC Analysis question

      Hi guys.

      I try create table which show ABC ABC analysis for sales

      short explanation

       

      We need determine category of each product twice based on accumulated percents builded on Amount of sales and on number of saled units

      Category A - Accumulated pct =< 80%

      Category B - Accumulated pct =< 95%

      Category C - Accumulated pct > 95

       

       

      ProductSalespctAccumulated pctABC
      Product1300050%50%A
      Product2200033%83%B
      Product3100017%100%C

       

       

      I can calculate ABC categories for Sales. But cant find solution to combine calculation per sales and per units in one table

      because when i add columns for saled units all of them sorted by Amount of sales instead saled units and Accumulated percent can't be calculated properly. Can anyboady suggest how i can sum percents based on rank of recods or reordering data inside expressions. Any ideas

        • ABC ABC Analysis question

          Hello,

          You can try using the AGGR statement for this.

          aggr( sum(Sold Units), Units ) this way it shows you the amount of the sold units per Unit.

          Hope this helps.

           

          Thanks

          ANDY

          • Re: ABC ABC Analysis question

            Hi,

             

            Please, take a look into attached example.

             

            Kind regards,

            Janusz

            • Re: ABC ABC Analysis question

              Hi,

               

              I have the same question as QVVLlogin. ABC over one dimension seem to be solved ok by Qlikview but the challenge is how to combine ABC for two or more dimensions. E.g. We want to see ABC by Sales Value compared with ABC by Contribution, Volume, Frequency etc. Extremely useful and powerful type of analysis.

               

              I have attached a spreadsheet that shows the concept and what we are trying to achieve.

               

              Questions:

              1) Is it possible to achieve this in a dynamic analysis in Qlikview, if so, how?

              2) If a static classification must be done during load script, how would this script look like?

               

              Best regards,

              Freddy

                • Re: ABC ABC Analysis question

                  Don't know how this can be achived with set expression or functions but.

                  I think this logic can be implemented in load script in following way

                   

                  1. Create several tables with ABC per Sales, Units, e.t.c.

                  2. Joining tables by ProductName

                  3. Creating a chart

                   

                  But how about different type of selection?

                  Is that's analisys?

                   

                  Maybe we can fill tables mentioned in the begining  with ABC calculations diring partial reload to decrease calculation time ?

                  this can be done with VB macro

                  But like for me it's realy tricky.

                  Any ideas?

                  • Re: ABC ABC Analysis question
                    Maxim Senin

                    Question to freddyjo - what does the Frequency in you example stand for? How often a product is ordered independently of sales volume or sales volume is taken into consideration?

                     

                    Thanks.