New to QlikView

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

Announcements
cancel
Showing results for
Did you mean:
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.

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
1 Solution

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

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

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

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

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

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

hi

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

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

Hi,