3 Replies Latest reply on May 26, 2016 8:26 AM by Bill Lyons

# IF THEN statement inside an IF THEN statement - Part II

This discussion is a continuation of: IF THEN statement inside an IF THEN statement

@Bill Lyons answered my question correctly, but I am still not getting the results I need. The conditional formatting is applying to each question, which is unusual since the participation rate is for entire survey admin so it's the same for all the question.

My goal is to color code (red,yellow, green, blue) results if participation is greater than 40%, if not show 'grey'. So in the example below, only Fall 14-15 should be color coded, all others should show grey.

When I apply this formula, which is the one I think should work:

IF SUM([Participation Rate]) > .4 THEN

IF [Strongly Agree %] < 0.2 THEN 'Red'

ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

ELSE 'NULL'

END

ELSE 'Grey'

END

I get this result:

When I apply this formula, for testing since participation rate shouldn't be over 100%:

IF SUM([Participation Rate]) > 1 THEN

IF [Strongly Agree %] < 0.2 THEN 'Red'

ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

ELSE 'NULL'

END

ELSE 'Grey'

END

I get this result:

When I apply this formula, for more testing:

IF SUM([Participation Rate]) > 2 THEN

IF [Strongly Agree %] < 0.2 THEN 'Red'

ELSEIF [Strongly Agree %] < 0.4 THEN 'Yellow'

ELSEIF [Strongly Agree %] < 0.6 THEN 'Green'

ELSEIF [Strongly Agree %] > 0.6 THEN 'Blue'

ELSE 'NULL'

END

ELSE 'Grey'

END

I get this result:

• ###### 1. Re: IF THEN statement inside an IF THEN statement - Part II

A lot of the answer to this depends on the structure and content of your data. If you can attach a sample packaged workbook (.twbx), we could give you a more definitive answer. If you have confidential data, please see Anonymize your Tableau Package Data for Sharing |Tableau Support Community.

The biggest question is, what is in your [Participation Rate] field at the row level? What you show appears to be aggregated. Is it not? If [Participation Rate] is already calculated and stored on each row, then you probably don't want to sum it. You might try MIN(), MAX() or AVG(). If [Participation Rate] is a calculated field, then you may want to try changing it to a Level of Detail (LoD) calculation. Which is appropriate here depends entirely on your data.

1 of 1 people found this helpful
• ###### 2. Re: IF THEN statement inside an IF THEN statement - Part II

AVG() worked! Thank you!