topic Re: Sales Overlap / Intersection problem in QlikView

Sales Overlap / Intersection problem

Wed, 31 Jul 2013 12:38:08 GMT

Hi all,

Consider the following table of products sold to various customer categories. Some of the products are exclusive to one customer category, hence only appearing once, and other products are shared among two or more categories:

Product Number	Customer Category
1001	Window manufacturer
1002	Door manufacturer
1003	Floor manufacturer
1004	Floor manufacturer
1005	Decking manufacturer
1006	Contractor
1007	Contractor
1007	Floor manufacturer
1008	Wholesale
1009	Wholesale
1009	Floor manufacturer
1009	Door manufacturer
1009	Contractor
1010	Door manufacturer
1011	Contractor
1012	Floor manufacturer
1012	Door manufacturer

Now I want to find out the number of distinct products (for each customer category) that are shared between customer categories, displayed on customer category. In fact the follwing table:

Customer Category	# of distinct articles that are shared by at least one other customer category
Window manufacturer	0
Door manufacturer	2
Floor manufacturer	2
Decking manufacturer	0
Contractor	2
Wholesale	2

I have tried the follwing expression in a straight table but it doesn't seem to be working:

Count(DISTINCT If(Aggr(Count (DISTINCT [Customer Category]), [Product Number])>1, [Product Number] ) )

Could someone give me a push in the right direction?

Thank you,

David

Re: Sales Overlap / Intersection problem

Anonymous — Wed, 31 Jul 2013 12:44:47 GMT

Maybe this (assuming that Customer Category is table dimension):

if(count(distinct [Product Number])=1, 0, count(distinct [Product Number]))

Re: Sales Overlap / Intersection problem

Wed, 31 Jul 2013 13:01:21 GMT

Hi Michael,

Thanks for your reply!

However the suggested result does not render the expected outcome.

BR,

David

Re: Sales Overlap / Intersection problem

Anonymous — Wed, 31 Jul 2013 13:03:39 GMT

David, what are you getting?

Re: Sales Overlap / Intersection problem

Wed, 31 Jul 2013 13:10:51 GMT

Sorry for the messy tables, but this is what I am getting:

Category	if(count(distinct [Product])=1, 0, count(distinct [Product]))
Contractor	4
Door manufacturer	4
Floor manufacturer	5
Wholesale	2

With my above suggestion I get the following:

Category	Count(DISTINCT If(Aggr(Count (DISTINCT Category), Product)>1, Product ) )
Contractor	1
Floor manufacturer	1
Wholesale	1

Thanks,

Re: Sales Overlap / Intersection problem

Wed, 31 Jul 2013 13:27:31 GMT

I just realized I made a mistake in the expected outcome. So this is what I want it to look like:

Category #of distinct articles shared by other categories

Window manufacturer 0

Door manufacturer 2 (product nr: 1009, 1012)

Floor manufacturer 3 (product nr: 1007, 1009, 1012)

Decking manufacturer 0

Contractor 2 (product nr: 1007, 1009)

Wholesale 1 (product nr: 1009)

Sorry for the mishap

Re: Sales Overlap / Intersection problem

Anonymous — Wed, 31 Jul 2013 13:44:28 GMT

I just understood what you want - count products per Category, but include only the Products which exist in more than one Category.

Is it correct?

Re: Sales Overlap / Intersection problem

Wed, 31 Jul 2013 13:46:40 GMT

Correct! The count of distinct products that exists in more than one category displayed per category

Re: Sales Overlap / Intersection problem

Anonymous — Wed, 31 Jul 2013 14:12:02 GMT

I got it - see attached.

Expresion is using set, and I created a variable with another expression.

Regards,

Michael

Re: Sales Overlap / Intersection problem

Thu, 01 Aug 2013 06:20:50 GMT

Thank you Michael!!

Works like a charm 🙂

Re: Sales Overlap / Intersection problem

Thu, 01 Aug 2013 08:35:48 GMT

Hi again Michael,

Sorry to bother you..... but I have stumbled across a problem. For certain products (in my dataset) the field "Product Number" are not really a number but rather a textstring, now this is not a problem in my case because I can elimate them since they are not of interest. Although it would be great to know if there is any way of making the above solution work also for cases where "Product Number" is not actually a number.

However, for a specific entire country (and this I can't be without) the "Product Number" field is named as "3-27278", "2-55448", in fact a "-" sign in the string. This also causes the above solution to fail. Is there any way of working around this issue or adapt the above solution to this scenario?

Thanks for your time and your help!

David

Re: Sales Overlap / Intersection problem

Thu, 01 Aug 2013 09:24:01 GMT

Solved it using PurgeChar() function, for my latter problem!

Again, thanks for all the help Michael!

Re: Sales Overlap / Intersection problem

Anonymous — Thu, 01 Aug 2013 13:21:39 GMT

It will work with text if change the variable a little, enclosing each value in single quotes:

=chr(39) & concat(distinct

if(aggr(count(distinct "Customer Category"),"Product Number")>1,

"Product Number")

, chr(39) & ',' & chr(39)) & chr(39)