If you can pivot your data you can do something like this:
I've just dragged colour onto text and sorted by a field - value descending - then added a comma into "Text" label. NULL values filtered out.
I did the pivot in the data window:
This is okay if you can pivot, don't mind the extra comma on the end and were happy for the result to be on the view and not a calculated field to be used elsewhere.
How well does that suit your needs? Post back and someone will help out more if need be!
PS. Workbook attached (v 2020.1)
PPS. If you're after a calculation based method then you can use table calculations - TABLEAU GURUS: Combining Seperate Rows into Single Row List
cust-colour-rank.twbx 12.9 KB
This is great - thank you. I did not know you could pivot in Tableau, so that definitely solved a lot of problems.
Just one question on the output order, how can I make it show in order of the largest value for each Row?
Your output for Customer 2 shows as: Green, Red, Black, White - 0.1, 0.2, 0.5, 0.2
The output i would like: Black, Red, White, Green or Black, White, Red, Green - 0.5, 0.2, 0.2, 0.1
Ah no, for some reason I’m not getting all the usual email updates from forum responses so thanks for the @ mentio!
I actually thought I’d handled to sort order, but as you point out it doesn’t look right! You’ll see in my screenshot that there is a sort on the “colour” pill: I dragged colour onto text and then right clicked it and sorted by a field - picking “value“ and “descending”. But perhaps I messed this up before doing the screenshot. Did you give that a go?
Hi Steve Wood,
No problem at all, thanks for your reply.
Yes, I did try this with my real data however similar to your screenshot above I think this is sorting it in order of column total i.e. Green will always come first if it appears for that row because the total of that column is 2.
Instead I need to be able to sort it order by largest to smallest for each row. I also tried several LOD calculations to attempt to force tableau to calculate this by each row and using that as the sort, but I still haven't found out what the problem is yet.
I see the problem. It's sorting by the overall total for each colour, which makes perfect sense now I stop and think about it. This sheet illustrates:
I have another option for you using table calculations rather than LOD expressions, as we can make use of the PREVIOUS_VALUE function to build up a comma separated list. I have a feeling I've over complicated this a little but here's the rough steps.
First up I've created a calculation called [i] which is just = INDEX(). I've dragged that on to columns, then pulled colour and value onto the values/text. When you do this is won't initially look like the below...
I had to right click the [i] pill on columns and selected edit table calculation. Then I was able to compute using specific dimensions; reorder reference and colour so that I could restart every reference (so each customer get's their own unique index/ranking essentially); finally I had to set a custom sort order on Value - SUM - descending.
This should now mean that for each customer the colours are sorted into their positions [i] based on descending order of value.
From here we can switch the text shown over to a calculation using PREVIOUS_VALUE...
I created a calculation [Colour list] which =
IF NOT ISNULL(SUM([Value]))
THEN IF PREVIOUS_VALUE('') != '' THEN ', ' ELSE '' END + MIN([Colour])
PREVIOUS_VALUE uses the previous value of the whole calculation in the partition, or the value passed in to start with. So above you see that we concatenate each colour onto the PREVIOUS_VALUE if that colour has a non-NULL value. There is also some messing about to ensure that we only get a comma between values.
I dragged this onto Text in place of Colour/Value, but retained Colour on detail. I then had to edit the table calculation (like I did for [i] above) to calculate/restart in the right order.
In the above we get to see Tableau's workings as we read across - so we see Black in spot one, then Black, Red and so on. Obviously we really only want the final iteration to show. So the last step is to add a filter on another table calculation to just show the last [i] column - again this needs editing to calculate/restart in the right order.
[Final list?]: LAST()=0
Filtered to TRUE
Seems a bit long winded eh! An alternative is to fix this up in some data prep steps I guess?
PS. Workbook attached.
cust-colour-rank.twbx 35.3 KB
Thanks Steve - this solution worked perfectly for me.