topic Aggregate Size (ClassesABC %) in QlikView

Aggregate Size (ClassesABC %)

Wed, 15 Sep 2010 08:10:01 GMT

I create classes ABC buyers in size.
Class A should be buyers making 80% of sales, class B buyers making 15% of sales and class C on 5% of sales.
Can you help

ex:

LOAD * INLINE [
Buyer, Sales

jonh, 100
mario, 22
luca, 32
ven, 3
trist, 213
giulia, 21
andrea, 54
manuel, 76
antonio, 76
enrico, 765
fabio, 54
valeria, 43
lino, 98
pino, 5
tino, 4
serio, 3453
];

Aggregate Size (ClassesABC %)

Miguel_Angel_Baeyens — Wed, 15 Sep 2010 09:22:08 GMT

Hello Andrea,

You may use something like the following expression as dimension in your chart:

=If(aggr(Sum(Sales) / Sum(TOTAL Sales), Buyer) * 100 > 80, 'A', If(aggr(Sum(Sales) / Sum(TOTAL Sales), Buyer) * 100 > 15, 'B', If(aggr(Sum(Sales) / Sum(TOTAL Sales), Buyer) * 100 > 5, 'C', 'Other' )))

and

Count(DISTINCT Buyer)

as expression.

It should return a table where you have different classes and the number of buyers per class.

Hope that helps.

Aggregate Size (ClassesABC %)

Wed, 15 Sep 2010 10:42:27 GMT

THANKS

BUT I do not returned correctly.

An example

LOAD * INLINE [
Buyer, Sales
jonha, 100
maria, 22
lucaa, 32
vena, 3
trista, 213
giulio, 21
andre, 54
manuela, 76
antonia, 76
enrica, 765

jonh, 100
mario, 22
luca, 32
ven, 3
trist, 213
giulia, 21
andrea, 54
manuel, 76
antonio, 76
enrico, 765
fabio, 54
valeria, 43
lino, 98
pino, 5
tino, 4
serio, 3453
];
[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/7711.TEST1.xls:550:0]

Aggregate Size (ClassesABC %)

Miguel_Angel_Baeyens — Wed, 15 Sep 2010 10:57:46 GMT

Hello Andrea,

Using your inline sample table it returns proper values to me with the above expression corrected (check it, I wrote "< 5" instead of "> 5" for class "C"). I'm getting no "A", one "B" ("serio"), two "C" ("enrica" and "enrico") and the rest of buyers as "Others". The percentages match exactly the ones in your spreadsheet.

Regards,

Aggregate Size (ClassesABC %)

Wed, 15 Sep 2010 11:46:51 GMT

Miguel thanks but I do not get what I want.
Serious, Enrica Henry is class A
because the sum of their sales is almost 80% of all sales.

serio	3453	54,11%	54,11%	A
enrica	765	11,99%	66,10%	A
enrico	765	11,99%	78,09%	A
trist	213	3,34%	81,43%	B
trista	213	3,34%	84,77%	B
jonh	100	1,57%	86,33%	B
jonha	100	1,57%	87,90%	B
lino	98	1,54%	89,44%	B
antonia	76	1,19%	90,63%	B
antonio	76	1,19%	91,82%	B
manuel	76	1,19%	93,01%	B
manuela	76	1,19%	94,20%	B
andre	54	0,85%	95,05%	B
andrea	54	0,85%	95,89%	B
fabio	54	0,85%	96,74%	C
valeria	43	0,67%	97,41%	C
luca	32	0,50%	97,92%	C
lucaa	32	0,50%	98,42%	C
maria	22	0,34%	98,76%	C
mario	22	0,34%	99,11%	C
giulia	21	0,33%	99,44%	C
giulio	21	0,33%	99,76%	C
pino	5	0,08%	99,84%	C
tino	4	0,06%	99,91%	C
ven	3	0,05%	99,95%	C
vena	3	0,05%	100,00%	C

Aggregate Size (ClassesABC %)

miikkaqlick — Wed, 15 Sep 2010 13:36:27 GMT

Reason You are not getting what You want is that Miguel's dimension returns values 54%, 11%, 11%, 3%. Not 54%, 65%, 76%, 79%.

I made a combo chart that shows what You want. Bar and line graph, Buyer as dimension. 2 expressions 1. =Sum(Sales) 2. =Sum(Sales) / Sum(Total Sales) *100.

Put full accumulation on for 2. expression and put axis on right for 2. expression. Sort buyers by expression Sum(Sales).

Now You should have graph that has 2 x-axes. Left is number of sales, right is precentage of sales. From this we can see that 80% line is broken between enrico and trist.

If You want to get approximately right values using Miguels dimension for this case then You should change limits to:

> 10, 'A'

> 1 , 'B'

>0, 'C'

Miikka

Aggregate Size (ClassesABC %)

Wed, 15 Sep 2010 14:31:51 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/1030.TEST-3.xls:550:0]Dear Miikka,thank!

The problem is that I have to create classes in size and not in expressions.
Yours is a trick that works in the sample who reported not follow example.
Can you help me?

LOAD * INLINE [
Buyer, Sales
jonhax, 100
mariax, 22
lucaax, 32
venax, 3
tristax, 213
giuliox, 21
andrex, 54
manuelax, 76
antoniax, 76
enricax, 765

jonhaz, 100
mariaz, 22
lucaaz, 32
venaz, 3
tristaz, 213
giulioz, 21
andrez, 54
manuelaz, 76
antoniaz, 76
enricaz, 765

jonha, 100
maria, 22
lucaa, 32
vena, 3
trista, 213
giulio, 21
andre, 54
manuela, 76
antonia, 76
enrica, 765

jonh, 100
mario, 22
luca, 32
ven, 3
trist, 213
giulia, 21
andrea, 54
manuel, 76
antonio, 76
enrico, 765
fabio, 54
valeria, 43
lino, 98
pino, 5
tino, 4
serio, 3453
];

thanks

Aggregate Size (ClassesABC %)

Wed, 15 Sep 2010 15:10:22 GMT

for me it is impossible without using an export!
I accept denials

Aggregate Size (ClassesABC %)

hector — Wed, 15 Sep 2010 15:56:42 GMT

Hi, i've made this, the only problem that i see, is that the ABC classif is pre-calculated

i hope this can be useful for you

At the left, the straight table with the full accumulation and at the right the pre-calculated acc.%

The "benefit" of this report, is that you can use ABC code as a dimension

rgds

Aggregate Size (ClassesABC %)

Thu, 16 Sep 2010 08:05:14 GMT

hello Hèctor,

your solution is good but is not dynamic.

I need to change classes on the screen without reload data.

Aggregate Size (ClassesABC %)

miikkaqlick — Thu, 16 Sep 2010 11:20:28 GMT

Hi!

I don't think it's possible to change dimensions calculations without reloading data. Or I don't know...

Slight improvement for Hèctor's script:


INPUTFIELD A_line;
INPUTFIELD B_line;
//--Snipped some code away --- //
t1:
Load
 *,
 if([Acc. Percentage]<=$(A_line),
 'A',
 if([Acc. Percentage] > $(A_line) AND [Acc. Percentage]< $(B_line),
 'B',
 'C'
 )
 ) as ABC
;

[\code]
This way You can add 2 inputfields to layout and change values from there. You still have to reload application for changes to take effect.

BR,
Miikka</body>

Aggregate Size (ClassesABC %)

Thu, 16 Sep 2010 11:33:02 GMT

Miikka,thank you very much but
The problem is that I can not reloading.

I don't give up