beast mode - counting text

user055735

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!

GrantSmith

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

 

jaeW_at_Onyx

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.

user055735

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

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

 

jaeW_at_Onyx

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.

user055735

Thanks! This seems to have worked.