Using Aggr function to display Customers with C-Materials only

[Anonymous]

Dear all,

first of all: this is my first post here and I think this is a great community!

As I have been struggling with this issue for quite a while now, I decided to register myself, hoping you can help me solve my issue.

I assume it's not a big thing, though, I am stuck here.

Plot:

I created an ABC-analysis in order to classify materials based on their importance with regards to total sales volume.

I have Customer data available to match these materials to the respective customers.

What I need to achieve:

As a matter of fact, there are some customers, which receive A, B & C-classified materials. However, I want to display those customers only which receive "C"-classified materials only.

I assume, aggr function will help here. Unfortunately, I do not know how to use it.

Thanks a lot in advance for your help!

felipeflamenco

Ship-To	ABC_Material	count(distinct(Material))
Customer 1	A	1
Customer 2	A	2
Customer 3	A	1
Customer 4	A	5
Customer 4	B	2
Customer 4	C	1
Customer 5	A	4
Customer 5	A	3
Customer 6	A	4
Customer 6	A	4
Customer 7	A	9
Customer 7	B	1
Customer 8	A	5
Customer 9	A	7
Customer 9	B	1
Customer 10	A	10
Customer 10	B	4
Customer 10	C	1
Customer 11	A	3
Customer 12	A	1
Customer 12	C	1
Customer 13	A	1
Customer 14	A	1
Customer 15	B	1
Customer 15	C	1
Customer 16	A	1
Customer 17	C	1
Customer 18	A	2
Customer 19	A	2
Customer 19	B	2
Customer 20	A	1
Customer 20	C	3

[settu_periasamy]

Do you want to show the customer which is 'C'..

Try this

Dimension : Ship-to

Expression :  =Only({<ABC_Material={'C'}>}[Ship-To])

if you want to show the count..

Dimension : Ship-to

Exp : =Count({<ABC_Material={'C'}>}[Ship-To])

[Anonymous]

Hi Settu,

thank you. This is an interesting approach.

However, these expressions just help me identify customers which receive "C" materials.

What I want to achieve, though, is to identify those customers which receive "C" materials only.

Any further suggestions?

Thank you and best regards

[settu_periasamy]

Hi,

Try this

=if(ABC_Material='C', Aggr(Only({<ABC_Material={'C'}>}[Ship-To]),[Ship-To]))

[Anonymous]

Hi Settu,

thanks a lot! At first I thought that's what I was actually looking for.. unfortunately, it's not. 🙂

Still an intersting string.

[Marcellino_Groothof]

Hi,

Try this in 3 expressions:

sum (CountMaterial) ,

sum (TOTAL <Material> CountMaterial)  Total Material (header)

sum (TOTAL {$<Material={'C'}>} CountMaterial)   Total C (header)

Kind Regards

see atachment

[Anonymous]

Hi Marcellino,

thank you.

So, I'll go with

Dimension: [Ship-To]

How would I need to set the commas between the different parameters for the Expressions correctly? The way you have presented it, will not work.  Also, putting a sum() before a count() won't work either.

Regards

[Anonymous]

hi

Use Count({<ABC_Material={'C'}>}[Ship-To])

[Anonymous]

Hi Neetha,

thank you. Unfortunately, this will help me identify customers which receive "C" materials. Yet, I want to identify only those customers which receive "C" materials only.

--> So, the result of the expression for this small example should be "Customer 17" only.

[Marcellino_Groothof]

Hi,

see attachment, I hope this will help you further.