Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 |
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
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
Solved it using PurgeChar() function, for my latter problem!
Again, thanks for all the help Michael!
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)