beast mode - counting text

Member

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!

• Coach

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(CASEWHEN `Why did you decide to not enrol?` LIKE '%I cannot currently afford it%' THEN 1ELSE 0END)`

Not interested:

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

No time:

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

Other:

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

**Did this solve your problem? Accept it as a solution!**

• Coach

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

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"
• Member

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.

• Coach

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(CASEWHEN `Why did you decide to not enrol?` LIKE '%I cannot currently afford it%' THEN 1ELSE 0END)`

Not interested:

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

No time:

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

Other:

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

**Did this solve your problem? Accept it as a solution!**
• Coach

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"
• Member

Thanks! This seems to have worked.

This discussion has been closed.