Text unique values - only count for each option

zgranperc

I'm trying to create a table that shows the total number of times a person selected 1 of the 5 available options from a multi-select field.

Audience field options:

• C-Suite
• VP
• Director
• Manager
• Below Manager

Right now the table is showing me all the possible unique values by row but I want the amount for each value for all available rows.

What's the best way to show this value?

RobSomers

@zgranperc With your Audience field having multiple options in each row, you're going to need to do a beast mode for each option. First one would be:

SUM(case when 'Audience' like '%C-Suite%' then 1 else 0 end)

Then just repeat, looking for the corresponding option for each one. You'll need to be careful on the Manager one and use:

'Audience' like '%Manager%' and 'Audience' not like '%Below%'

RobSomers

@zgranperc With your Audience field having multiple options in each row, you're going to need to do a beast mode for each option. First one would be:

SUM(case when 'Audience' like '%C-Suite%' then 1 else 0 end)

Then just repeat, looking for the corresponding option for each one. You'll need to be careful on the Manager one and use:

'Audience' like '%Manager%' and 'Audience' not like '%Below%'

GrantSmith

Alternatively you could restructure your data so you have one record for each audience to allow you to count, filter and group by the different audience types easier.

zgranperc

@GrantSmith How would I go about restructuring the data?

GrantSmith

Having one audience per record. Something like:

C-Suite | 19 | Enterprise

VP | 19 | Enterprise

Director | 19 | Enterprise

You could utilize Magic ETL 2.0 formula tiles to use the SPLIT_PART function and grab each entry in your CSV list and then append each of those formula tile outputs to each other.

jaeW_at_Onyx

Imagine you had

C-Suite , VP, Director | 19 | Enterprise, Supply Chain

You'd need to duplicate the row a total of 6 times.

take the first column and duplicate each row by the number of Commas +1 (because that's how many respondents there are

In this example ,you'd want to duplicate that row 3 times. then use split-part() to keep the 1st, 2nd, and 3rd value.

you'd be halfway there b/c now you'd have

C-Suite | 19 | Enterprise, Supply Chain

VP | 19 | Enterprise, Suppy Chain

Director | 19 | Enterprise, Supply Chain

So then do the same process again on the 3rd column

C-Suite | 19 | Enterprise

C-Suite | 19 | Supply Chain

VP | 19 | Enterprise

VP | 19 | Supply Chain

...

use @MarkSnodgrass video as an example:

https://www.youtube.com/watch?v=sXOTfUnOUVU