6 Replies Latest reply on Jul 12, 2018 11:59 AM by Michael Prokop

# CASE Statement with "Between"

I need to essentially create bins for one of my measures but since the measure is a calculated field, from what I understand and can see you cannot bin a calculated field.

My calculated field essentially just calculates a percentage and I need to use this number to create a bar chart that indicates what % of my total population falls in each bin, ie, 10 people are between 0 and 20% complete, 15 people are between 21 and 40%, etc.

I had been attempting to use the following calculation but cant find anything on how to get it to work correctly.  Tableau is giving me the error "Expected 'THEN' to match 'CASE'"

CASE [Total Completed] WHEN between 0 AND 20 THEN "0-20%"

CASE [Total Completed] WHEN between 21 AND 40 THEN "21-40%"

CASE [Total Completed] WHEN between 41 AND 60 THEN "41-60%"

CASE [Total Completed] WHEN between 61 AND 80 THEN "61-80%"

CASE [Total Completed] WHEN between 81 AND 100 THEN "81-100%"

ELSE 0

END

• ###### 1. Re: CASE Statement with "Between"

Try this:

IF [Total Completed] >= 0 AND  [Total Completed]<= 20 THEN "0-20%"

ELSEIF [Total Completed] >= 21 AND [Total Completed]<= 40 THEN "21-40%"

ELSEIF [Total Completed] >= 41 AND [Total Completed]<=60 THEN "41-60%"

ELSEIF [Total Completed] >= 61 AND [Total Completed]<=80 THEN "61-80%"

ELSEIF [Total Completed] >= 81 AND [Total Completed]<=100 THEN "81-100%"

ELSE 0

END

• ###### 2. Re: CASE Statement with "Between"

CASE [Total Completed]

WHEN >= 0 AND <=20 THEN "0-20%"

WHEN > 20 AND <=40 THEN "21-40%"

WHEN >40 AND <=60 THEN "41-60%"

WHEN > 60 AND <=80 THEN "61-80%"

WHEN > 80 AND <=100 THEN "81-100%"

ELSE 0

END

• ###### 3. Re: CASE Statement with "Between"

This brings back an error "expected type string, found integer. Result types from 'IF' expressions must match."  I believe this is why I have to use CASE.

• ###### 4. Re: CASE Statement with "Between"

Sorry, that's due to the ELSE 0.  Change that to:

ELSE '0'

• ###### 5. Re: CASE Statement with "Between"

This brings back error "syntax error (maybe you are missing an identifier)" which shows the issue at the first ">" symbol

• ###### 6. Re: CASE Statement with "Between"

Full calculated field.  Try this and let me know if it works:

IF [Total Completed] >= 0 AND  [Total Completed]<= 20 THEN "0-20%"

ELSEIF [Total Completed] >= 21 AND [Total Completed]<= 40 THEN "21-40%"

ELSEIF [Total Completed] >= 41 AND [Total Completed]<=60 THEN "41-60%"

ELSEIF [Total Completed] >= 61 AND [Total Completed]<=80 THEN "61-80%"

ELSEIF [Total Completed] >= 81 AND [Total Completed]<=100 THEN "81-100%"

ELSE "0"

END