Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

felipeflamenco
New Contributor III

Aggr function to show customers with "C"-Materials only

Dear All,

first of all: I hope this is not a double post. However, I cannot find my last discussion posted about the same issue.

I am new to this community and hope you can help me solve my current issue. I assume for the more experienced this should not be a big issue - however, I have been struggling with this for quite a while now.

Plot:

I have created an ABC-analysis in order to classify materials according to their relative importance as part of total sales volume.

I have customer data available to identify which materials have been shipped to which customers.

What I want to achieve:

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

I assume the aggr function will help here. Unfortunately, I have not yet found out HOW.

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

Re: Aggr function to show customers with "C"-Materials only

Here's a workaround-solution:

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

Regards

View solution in original post

4 Replies
ogster1974
Honored Contributor II

Re: Aggr function to show customers with "C"-Materials only

Why not add a filter on your sheet on ABC_Material and select C.  Your table will then only display customers which receive C class materials.

Seems like you are trying to hardcode answers the user could find out easily for themselves using the power of the Qlik tool.

felipeflamenco
New Contributor III

Re: Aggr function to show customers with "C"-Materials only

Hi Andy,

thanks for your reply. Unfortunately, that's not what I was looking for.

This selection would only help me identify customers which receive "C" materials.

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


I had a double-post discussion topic here where we are discussing the same issue..

Will post an update as soon as we've found a solution.

Best regards

felipeflamenco
New Contributor III

Re: Aggr function to show customers with "C"-Materials only

Here's a workaround-solution:

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

Regards

View solution in original post

Re: Aggr function to show customers with "C"-Materials only

maybe helpful:

QlikCommunity_Thread_179024_Pic2.JPG

QlikCommunity_Thread_179024_Pic1.JPG

regards

Marco