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

  • RobSomers
    RobSomers 🟢
    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!**

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    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!**
  • @GrantSmith How would I go about restructuring the data?

  • GrantSmith
    GrantSmith Indiana 🥷

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    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

    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"