1 2 Previous Next 24 Replies Latest reply on Mar 15, 2016 1:33 PM by Diogo Braga

# SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

The data set is from a 6 point Likert scale survey responses.

I have percentages for strongly agree, agree, somewhat agree, somewhat disagree, disagree, and strongly disagree

I need to create a calculated field to calculate the Total Agree.

I unsuccessfully tried...

IF [Response] = "Strongly Agree" "Somewhat Agree" "Agree"

THEN

SUM [Response]

END

• ###### 1. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Would either of these get you where you need to be?

IF ATTR([Response]) = "Strongly Agree" OR ATTR([Response]) = "Somewhat Agree" OR ATTR([Response]) = "Agree"

THEN

COUNT([Response])

END

IF [Response] = "Strongly Agree" OR [Response] = "Somewhat Agree" OR [Response] = "Agree"

THEN 1

ELSE 0

END

• ###### 2. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Thanks for the response Joshua. I am not looking for a count though.

Let's say...if strongly agree = 42%, agree =  32%, and somewhat agree = 18%

The calculation for Total Agree would be = 42% + 22% + 18% = 92%

I hope you can help! Thank you!

• ###### 3. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Diogo,

I would need to see an example workbook, or at least have a better idea as to how your data and calculated fields are structured to provide more intuitive insight.

Your previous calculation shows that if [Response] is equal to a string.. and you can't sum a string, you can only count them.

Your new formula show that strongly agree is instead a % calculation, so my next guess would be to add [strongly agree] + [agree] + [somewhat agree]...

See how I'm getting confused now?

Thanks for any clarification you can provide!

Cheers!

• ###### 4. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Hi,

Not sure if this is a long term solution, but if you just want a quick topline figure, I'd put the scale points you want in a group, as in the attached.

Sam

• ###### 5. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Thanks for the reply Sam. Where is the attachment?

• ###### 6. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

I inserted the workbook to the question.

My goal is to create a calculation for Total Agree, then overlay Total Agree and Strongly Agree circles over the stacked bar chart as in the image below.

Thank you!

• ###### 7. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Ok, I think I'm on to something, but I can't quiet get the formatting to work.

Using this calculation:

{ FIXED [Metric] :

SUM(

IF [Response] = "Strongly Agree"

OR [Response] = "Somewhat Agree"

OR [Response] = "Agree"

THEN [Value]

ELSE 0

END

) / SUM({SUM([Value])}) }

to get the sum of Total Agree and a dual Axis I get to this point:

I am also using this formula:

to get the % of Total per each response group.

Please see the attached workbook for the progress. But I am stuck on how to provide the bubble for Strongly Agree, since you can't have 3 axis in a dual axis.

I will keep working on it as time permits.

Cheers!

2 of 2 people found this helpful
• ###### 8. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Oh, I forgot to mention that I am using Tableau 9.2

• ###### 9. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Thanks for all your work so far. It looks like we are getting close.

I must mention that the sample data set I sent you has only one metric, but the real data set contains 8 different metrics. When I applied your calculation to the real data set it didn't work, I think because of FIXED [Metric]. Also, your calculation is showing a Total Agree for each response, which doesn't look right. All we need is one number that represents the Total Agree.

Thanks again!

• ###### 10. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Thanks Sam! Your approach sums the Agrees, but I couldn't manage how to make it work for the visual I am trying to create. Any ideas on that?

• ###### 11. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

This should side step the fixed part.

And then removing the response color set from the total agree mark card.

Cheers!

2 of 2 people found this helpful
• ###### 12. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Hey Diogo-

This meet what you want?

3 of 3 people found this helpful
• ###### 13. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Thanks Adam! That's perfect for the sample data set I shared here. The calculations didn't add up correctly in the real data set. Not sure if I missed something from your setup or maybe because the real data set is a bit different because it contains different years, different seasons (Fall and Spring), more metric names. I will get back toyou Monday on this! As always thanks for the quick response!

• ###### 14. Re: SUM IF Responses = "Strongly Agree" "Somewhat Agree" "Agree"

Good morning!

It looks like we are getting closer. The 'Total Agree' and 'Strongly Agree' calculations are not adding up correctly in the sample data file. You can easily see that when filtering by school. Both axis should be 0.0 to 1.0, but that's not the case. I can't figure out what's causing this yet.

Also I applied the same calculations suggested to the real data file for testing but I wasn't able to get the same results as the sample data file. It's weird.

I am attaching both files for easy comparison. I hope you guys can help me find a solution for this!

Thank you!

1 2 Previous Next