topic Re: ABC ABC Analysis question in QlikView

ABC ABC Analysis question

Wed, 01 Jun 2011 12:19:16 GMT

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

Product	Sales	pct	Accumulated pct	ABC
Product1	3000	50%	50%	A
Product2	2000	33%	83%	B
Product3	1000	17%	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

Wed, 01 Jun 2011 12:26:18 GMT

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

Wed, 01 Jun 2011 15:24:28 GMT

Hi,

Please, take a look into attached example.

Kind regards,

Janusz

Re: ABC ABC Analysis question

Thu, 02 Jun 2011 11:27:45 GMT

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

Thu, 02 Jun 2011 11:48:50 GMT

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

maksim_senin — Tue, 10 Apr 2012 03:36:36 GMT

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.