2 Replies Latest reply on Jan 25, 2017 6:50 AM by Daniel Baker

# Multiple IF ELSEIF statements for conditional formatting

Hello,

I am building a scatterplot and trying to color code the marks to show their values within a range.  I have tried the calculated field below, but it only shows the first section of if statment.  My goal is to show 3 different colors of marks.  If a value is within this range be green, if within the next range, yellow, and the last range red.  Thanks for your help on this. My attempt is below.  Thanks.

IF[\$ change]< 20.01 then "Green" ELSEIF[\$ change] > -20.01 then "Green" ELSEIF[\$ change]<40.01 then "Yellow"

ELSEIF[\$ change]>-40.01 then "Yellow" ELSEIF[\$ change]>40.01 then "RED" ELSEIF[\$ change]<40.01 then "Red" END

• ###### 1. Re: Multiple IF ELSEIF statements for conditional formatting

Stephen,

When you use an IF statement, you must remember that the statements are calculated in the order in which they are appear.  This means that you should start at the extremes, not in the middle.  I have rearranged your formula as follows:

IF [\$ change] > 40.01 OR [\$ change] < -40.01 THEN "Red"

ELSEIF [\$ change] > 20.01 OR [\$ change] < -20.01 THEN "Yellow"

ELSE "Green"

END

Let me know if this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

4 of 4 people found this helpful
• ###### 2. Re: Multiple IF ELSEIF statements for conditional formatting

So, could you say in other words that if you have multiple elseif statements that once the first condition is true it returns that value?  Let's pretend [sales]= 31

if [sales] > 10 then "good"

elseif [sales] > 20 then "great"

elseif [sales] >30 then "awesome"

end

This would return "good" because it was the first condition met?

Thanks for clarifying.

- Daniel