Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using Aggr function to display Customers with C-Materials only

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-ToABC_Materialcount(distinct(Material))
Customer 1 A1
Customer 2A2
Customer 3A1
Customer 4A5
Customer 4B2
Customer 4C1
Customer 5A4
Customer 5A3
Customer 6A4
Customer 6A4
Customer 7A9
Customer 7B1
Customer 8A5
Customer 9A7
Customer 9B1
Customer 10A10
Customer 10B4
Customer 10C1
Customer 11A3
Customer 12A1
Customer 12C1
Customer 13A1
Customer 14A1
Customer 15B1
Customer 15C1
Customer 16A1
Customer 17C1
Customer 18A2
Customer 19A2
Customer 19B2
Customer 20A1
Customer 20C3
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi all,

I found a workaround. 

I classified all customers into A, AB, AC, ABC, B, BC or C-customers.

This is how I did it:

  1. Create a Pivot Table
  2. Dimension: [Ship-to]
  3. Expression:

    if(count({<ABC_Material={'A'}>}distinct(Material))>=1,'A')
    &
    if(count({<ABC_Material={'B'}>}distinct(Material))>=1,'B')
    &
    if(count({<ABC_Material={'C'}>}distinct(Material))>=1,'C')

Thanks a lot for your support!

Regards


View solution in original post

11 Replies
settu_periasamy
Master III
Master III

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
Not applicable
Author

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
Master III
Master III

Hi,

Try this

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

Anonymous
Not applicable
Author

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
Contributor III
Contributor III

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
Not applicable
Author

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
Not applicable
Author

hi

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

Anonymous
Not applicable
Author

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
Contributor III
Contributor III

Hi,

see attachment, I hope this will help you further.