New to QlikView

Discussion board where members can get started with QlikView.

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-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
Tags (3)
1 Solution

Accepted Solutions
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

4 Replies
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.

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

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

MVP

regards

Marco