beast mode - counting text

I have a survey where the participants are able to select multiple options. 

For example: Why did you decide to not enrol?

Answers: I cannot currently afford it

I am not interested

I cannot make the time

Other

 

When I try to visualise this, it does not group single answers with cases where there are multiple answer choices selected. 

I want to be able to count each of these options. 

I was trying something like the following, but it does not give me the correct counts (which I can quickly do in excel).

 

CASE
WHEN `Why did you decide to not enrol?` LIKE '%I cannot currently afford it%' THEN COUNT('I cannot currently afford it')
ELSE `Why did you decide to not enrol?`
End

What am I doing wrong? Or is there a better way to do this?

 

Thanks!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @user055735 

    You'd need four separate beast modes to create 4 new columns to count each of the 4 individual answers. If you include the `Why did you decide to not enrol?` value itself on your chart then it will only work for that specific answer and not the entire set of answers for the question.

     

    Can't afford example:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I cannot currently afford it%' THEN 1
    ELSE 0
    END
    )

    Not interested:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I am not interested%' THEN 1
    ELSE 0
    END
    )

     

    No time:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I cannot make the time%' THEN 1
    ELSE 0
    END
    )

     

    Other:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%Other%' THEN 1
    ELSE 0
    END
    )

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • If you want your visualisation to be one series per response choice (i.e. 4 people picked choice A and 3 people picked choice B)

     

    You need to alter the granularity of your dataset from one row per question to one row per answer choice.

     

    use the COLLAPSE function in MagicETL.

    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"
  • From what I can tell collapse is for when you want to take multiple columns and make a single one.

    My multiple answers are in a single column.

     

    I have tried to filter out what I am looking for in ETL using contains and the string name.

    And that works for one filter, but not if i want to filter multiple different strings and have a count of each.

     

    I hope this makes sense. I'm very new to data analysis in this way. 

     

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @user055735 

    You'd need four separate beast modes to create 4 new columns to count each of the 4 individual answers. If you include the `Why did you decide to not enrol?` value itself on your chart then it will only work for that specific answer and not the entire set of answers for the question.

     

    Can't afford example:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I cannot currently afford it%' THEN 1
    ELSE 0
    END
    )

    Not interested:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I am not interested%' THEN 1
    ELSE 0
    END
    )

     

    No time:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%I cannot make the time%' THEN 1
    ELSE 0
    END
    )

     

    Other:

    SUM(
    CASE
    WHEN `Why did you decide to not enrol?` LIKE '%Other%' THEN 1
    ELSE 0
    END
    )

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • it would help if you provided a screenshot of a sample of data (column headers are most important)

    and a excel mockup of what you're trying to accomplish.

    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"
  • Thanks! This seems to have worked. 

This discussion has been closed.