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
I got it - see attached.
Expresion is using set, and I created a variable with another expression.
Regards,
Michael
Maybe this (assuming that Customer Category is table dimension):
if(count(distinct [Product Number])=1, 0, count(distinct [Product Number]))
Hi Michael,
Thanks for your reply!
However the suggested result does not render the expected outcome.
BR,
David
David, what are you getting?
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,
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
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?
Correct! The count of distinct products that exists in more than one category displayed per category
I got it - see attached.
Expresion is using set, and I created a variable with another expression.
Regards,
Michael
Thank you Michael!!
Works like a charm 🙂