Sales Overlap / Intersection problem

[#1]

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

[Anonymous]

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

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

[#3]

Hi Michael,

Thanks for your reply!

However the suggested result does not render the expected outcome.

BR,

David

[Anonymous]

David, what are you getting?

[#5]

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,

[#6]

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

[Anonymous]

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?

[#8]

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

[Anonymous · Accepted solution]

I got it - see attached.

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

Regards,

Michael

[#10]

Thank you Michael!!

Works like a charm 🙂