Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

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

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

Contributor III
Contributor III

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

Hi,

Try this

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

Contributor III
Contributor III

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.

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

Contributor III
Contributor III

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

Master
Master

hi

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

Contributor III
Contributor III

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.

Contributor III
Contributor III

Hi,

see attachment, I hope this will help you further.