topic Re: Using Aggr function to display Customers with C-Materials only in QlikView

Using Aggr function to display Customers with C-Materials only

Anonymous — Thu, 03 Sep 2015 09:55:22 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

settu_periasamy — Thu, 03 Sep 2015 16:18:05 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Thu, 03 Sep 2015 18:40:24 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

settu_periasamy — Fri, 04 Sep 2015 03:25:50 GMT

Hi,

Try this

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

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 07:51:02 GMT

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.

Re: Using Aggr function to display Customers with C-Materials only

Marcellino_Groothof — Fri, 04 Sep 2015 07:54:15 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 08:23:01 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 08:28:28 GMT

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

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 09:13:04 GMT

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.

Re: Using Aggr function to display Customers with C-Materials only

Marcellino_Groothof — Fri, 04 Sep 2015 09:46:15 GMT

Hi,

see attachment, I hope this will help you further.

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 09:49:02 GMT

Thanks a alot. Still a little difficult to read but a great approach.

Re: Using Aggr function to display Customers with C-Materials only

Anonymous — Fri, 04 Sep 2015 09:54:15 GMT

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:

Create a Pivot Table
Dimension: [Ship-to]
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