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

[#2]

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

[#3]

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

Again, thanks for all the help Michael!

[Anonymous]

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)