1 2 Previous Next 15 Replies Latest reply on Nov 13, 2018 3:01 PM by Mohamed Hersi

What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 9:46 AM

I have data with a percentage column. I want to visualize this percentage column either as a Map or a Treemap and COLOR CODE it according to the predefined percentage ranges shown below. I have tried the following CASE statement but Tableau says it is invalid. Any help would be much appreciated.

Predefined Color Codes:

case [fieldname]

WHEN >=30% THEN 'Red'

WHEN <20% -30% THEN 'Orange'

WHEN <10% - 20% THEN 'Light Orange'

WHEN 5% - 10% THEN 'Light Green'

WHEN 0% - 5% THEN 'Green'

WHEN <0% THEN 'Purple'

end

Regards

Mohamed

1. Re: What is the best Function/Statement for Color Coding Performance

Jim Dehner Nov 13, 2018 10:45 AM (in response to Mohamed Hersi)

try the statement below - your sybtax was not correct

case [fieldname]
WHEN >=30% THEN 'Red'
WHEN >20% THEN 'Orange'
WHEN >0% THEN 'Light Orange'
WHEN >5% THEN 'Light Green'
WHEN >0% THEN 'Green'
WHEN <0% THEN 'Purple'
end

Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Like Show 0 Likes(0)

Actions
2. Re: What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 11:22 AM (in response to Jim Dehner)

Dear Jim,

I think you meant >10 (see third line from top). I have chnaged it to >10, but it still shows an error as show in the screen shot below:
Like Show 0 Likes(0)

Actions
3. Re: What is the best Function/Statement for Color Coding Performance

Jim Dehner Nov 13, 2018 11:36 AM (in response to Mohamed Hersi)

What is the error? please attach your twbx workbook
Like Show 0 Likes(0)

Actions
4. Re: What is the best Function/Statement for Color Coding Performance

Jim Dehner Nov 13, 2018 11:38 AM (in response to Mohamed Hersi)

I realized that Case doesn't like the <> symbols - convert the statement to a conditional like if ... then ....elseif ...
type statement
Jim
Like Show 0 Likes(0)

Actions
5. Re: What is the best Function/Statement for Color Coding Performance

misba.masood Nov 13, 2018 11:52 AM (in response to Mohamed Hersi)

Hi Mohammed,

Can you please share what ERROR are you getting. Click on this red color message you will get the details of error.

Regards,
Like Show 0 Likes(0)

Actions
6. Re: What is the best Function/Statement for Color Coding Performance

Jim Dehner Nov 13, 2018 12:05 PM (in response to misba.masood)

yes I see the message - if you hover over it you can see what the error is
Like Show 0 Likes(0)

Actions
7. Re: What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 12:20 PM (in response to misba.masood)

The message is:
"Syntax error (may be you are missing an identifier)."

Thanks
Like Show 0 Likes(0)

Actions
8. Re: What is the best Function/Statement for Color Coding Performance

Jim Dehner Nov 13, 2018 12:24 PM (in response to Mohamed Hersi)

just convert from a case statement to an If ... then ...elseif statement - case does not like < or >
Like Show 0 Likes(0)

Actions
9. Re: What is the best Function/Statement for Color Coding Performance

misba.masood Nov 13, 2018 12:26 PM (in response to Mohamed Hersi)

case [fieldname]
WHEN[fieldname] >=30% THEN 'Red'
WHEN[fieldname]<20% and [fieldname]<30% THEN 'Orange'
WHEN[fieldname]>10% and [fieldname]<20% THEN 'Light Orange'
WHEN[fieldname] >5% and [fieldname]<10% THEN 'Light Green'
WHEN [fieldname]>0% and [fieldname]< 5% THEN 'Green'
ELSE 'Purple'
end

Kindly try this.
Like Show 0 Likes(0)

Actions
10. Re: What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 12:36 PM (in response to misba.masood)

No solution yet.
Like Show 0 Likes(0)

Actions
11. Re: What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 12:37 PM (in response to Jim Dehner)

Can you put this into context of my statement, please?
Like Show 0 Likes(0)

Actions
12. Re: What is the best Function/Statement for Color Coding Performance

Mohamed Hersi Nov 13, 2018 12:39 PM (in response to misba.masood)

There is a new message though this time:
"Expected type float, found boolean. Comparison in 'CASE' expression, must be float type."
Like Show 0 Likes(0)

Actions
13. Re: What is the best Function/Statement for Color Coding Performance

misba.masood Nov 13, 2018 12:46 PM (in response to Mohamed Hersi)

CASE [penta3 Coverage]
WHEN [penta3 Coverage]>=30 THEN 'Red'
WHEN [penta3 Coverage]>20 and [penta3 Coverage]<30 THEN 'Orange'
WHEN [penta3 Coverage]>10 and [penta3 Coverage]<20 THEN 'Light Orange'
WHEN [penta3 Coverage]>5 and [penta3 Coverage]<10 THEN 'Light Green'
WHEN [penta3 Coverage]>0 and [penta3 Coverage]<5 THEN 'Green'
ELSE 'Purple'
END

Can you please share, what are the column values of 'Penta3 Coverage'?
Like Show 0 Likes(0)

Actions
14. Re: What is the best Function/Statement for Color Coding Performance

Michael Prokop Nov 13, 2018 1:20 PM (in response to Mohamed Hersi)

IF [penta3 Coverage]>=.30 THEN 'Red'
ELSEIF [penta3 Coverage]<.20 and [penta3 Coverage]<.30 THEN 'Orange'
ELSEIF [penta3 Coverage]>.10 and [penta3 Coverage]<.20 THEN 'Light Orange'
ELSEIF [penta3 Coverage]>.5 and [penta3 Coverage]<.10 THEN 'Light Green'
ELSEIF [penta3 Coverage]>0 and [penta3 Coverage]< .5 THEN 'Green'
ELSE 'Purple'
end
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions