Concat and count

[Ken86]

Hi! 

My data is looking something like this (with alot more ID's and colors):

customer_ID	category
1	blue
1	red
2	blue
2	red
3	blue
3	red
3	yellow
4	blue
4	red
4	yellow
5	yellow
6	blue

I created a table and added =CONCAT({<category=>} DISTINCT category, ', '). And it looks something like this:

customer_ID	=CONCAT({<category=>} DISTINCT category, ', ')
1	blue, red
2	blue, red
3	blue, red, yellow
4	blue, red, yellow
5	yellow
6	blue

My next step, which I can't figure out. Is to create a new table with the this as end result:

blue, red	2
blue, red, yellow	2
yellow	1
blue	1

Number of cumster_ID's that has the same categories.

Pls help

Best regards,
Ken

[hic · Accepted solution]

Use

=Aggr(CONCAT({<category=>} DISTINCT category, ', ', category), customer_ID)

as dimension, and

=Count(distinct customer_ID)

as measure

[Ken86]

Works like a charm, thank you!!!