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

# 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

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.