I have a spreadsheet which contains two columns: one column, color, contains group information, and the other column, ID, contains a list of unique IDs that belong to that group. A spreadsheet is attached which contains this information. Note that one unique ID may belong to one group or multiple groups. Below is an unpivoted version of this table for reference purposes:
I need to perform two operations on this data.
(1) generate overlap counts for all possible combinations of groups, as shown below. Please note that the ID column is for reference only. It shows what IDs belong to that group and only that group. Once again, it is only for reference only. It is not needed in the final output. The only columns needed in the final output are the combinations list and the associated counts.
(2) generate 4 additional columns; the first column is percent total, which divides count for a given combination by the sum of counts across all combinations. the three remaining columns are the count column divided by the count of total unique ids in each group (so blue = 5, red = 6, and green = 6). Thus, my desired final output is:
I was able to accomplish this using R after quite a bit of work, but will need to build this dashboard using Tableau, so any help would be greatly appreciated. I have scoured these boards for a similar problem and have not found one so far. I did not include a Tableau workbook as I don't know where to ever start with this, outside of the base excel file.
Thank you very much!
In the same vein as Kaz' answer, how about something like this? It finds whether an id has the color or not, and if so concatenates that in the Combos string.
IF { FIXED [ID] : MAX(IF [Color] = 'BLUE' THEN 1 END) } = 1
THEN 'BLUE '
ELSE ''
END +
IF { FIXED [ID] : MAX(IF [Color] = 'GREEN' THEN 1 END) } = 1
THEN 'GREEN '
ELSE ''
END +
IF { FIXED [ID] : MAX(IF [Color] = 'RED' THEN 1 END) } = 1
THEN 'RED'
ELSE ''
END
Hope this helps,
Jennifer
