# 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!

• Indiana 🥷

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)`

• Budapest / Portland, OR 🟤

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.

• 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.

• Budapest / Portland, OR 🟤

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.

• Thanks! This seems to have worked.

This discussion has been closed.