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 Indiana 🔴
    Accepted 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
    )

     

Answers

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

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

     

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