Text unique values - only count for each option
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?
Best Answer
-
@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%'
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
3
Answers
-
@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%'
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
3 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
@GrantSmith How would I go about restructuring the data?
0 -
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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:
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive