Aggregate Size (ClassesABC %)

[#1]

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
];

[Miguel_Angel_Baeyens]

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.

[#3]

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]

[Miguel_Angel_Baeyens]

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,

[#5]

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

[miikkaqlick]

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

[#7]

[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

[#8]

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

[hector]

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

[#10]

hello Hèctor,

your solution is good but is not dynamic.

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