11 Replies Latest reply: Sep 16, 2010 7:33 AM by AndreaPeretti

# Aggregate Size (ClassesABC %)

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:

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 %)

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 %)

THANKS

BUT I do not returned correctly.

An example

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 %)

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 %)

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 %)

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 %)

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?

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 %)

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

• ###### Aggregate Size (ClassesABC %)

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 %)

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 %)

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 %)

Miikka,thank you very much but