Home » Tableau » Case Statement in Tableau

Case Statement in Tableau

What is the Case statement in Tableau ?

The CASE statement in Tableau is a function which is used to compares a values for particular expression (Field or parameter) in a sequence of values like, value1 , value2 , value3. to compares the expression it use the interlink technique to just compares the values and returns a result for matched value, If values not matches then it returns the by default values . If these is not By default value the it will return the NULL values.

CASE statement in Tableau cannot perform Boolean algebra, however it can be performed by IF condition in Tableau at the end of this tutorial you will see the example how IF condition works within Tableau Case statement.

What is the difference between IF and CASE in Tableau?

CASE statement is fast processing as compared with IF condition and CASE statement also is simple way to write the syntax. Case statement is designed in such a way that it interlinked with logics and gives the output based on given conditions to check the values in expression. Where IF condition use the boolean condition to compare values based on TRUE and FALSE condition.

When to use the Tableau CASE statement?

It’s good to use CASE statement when you have to checks many conditions and logics from one and two dimensions. Example, if you want to group countries from country field then you can use the Case statement. Second example, if you want to put conditions on different years for different profit scenarios then you can use case statements.

Case Syntax in Tableau

CASE <expression> 

WHEN <value1> THEN <result1> 
WHEN <value2> THEN <result2> 
WHEN <value3> THEN <result3> 
WHEN <value4> THEN <result4>

ELSE <default return>
END

Let’s understand the various components of CASE Statement in Tableau:

  • CASE is the keyword, which tells Tableau that on which expression, field or parameter should performed
  • <expression> represents the field or parameter which is  used to perform the case function
  • WHEN is the Keyword which tells Tableau if value exist for expression or not
  • <result1> is the result/value that will be returned if the expression matched with value.
  • THEN is the keyword indicates to return result value
  • ELSE is the Keyword which performed if non of the condition is matched,
  • END represents the end of the CASE function.

Key features of Case Statement in tableau

  • It helpful to the users to avoid mistakes like referring the fields and dimensions
  • Case statements much faster then IF statements
  • CASE statements are very easy to write and apply logics
  • CASE statement can not perform on Boolean conditions
  • Case statement in Tableau only compares the exact values
  • Case statement can not be performed on single line, User need to write formula with the multiple line

Lets take 7 different examples below to explain how Case statement in Tableau works:

Example #1: Simple Case Function

Let take example for Hotel data to categories the Room with cost for each room types, below are example used to categories room category with simple case function in tableau.

CASE  [Room Category]  

    WHEN 'Studio Room' THEN "Low cost" 

    WHEN 'Single Room' THEN "Medium cost"

    WHEN 'Double Room' THEN "High Cost"

 ELSE "Luxury"

END

Example #2: CASE statement in Tableau calculated field

You can use CASE statement in Tableau calculated field, In below CASE statement we have calculated the profit based on Case statement  for “Sales channel“. Although you can use direct formula also or calculated field for Profit.

But sometime you need calculation different for different members of field then you can use case to calculate calculated field for numeric calculation.

CASE [Sales Channel]

     WHEN 'Offline' THEN [Total Revenue]-[Total Cost]+ [Tax Charges]

     WHEN 'Online' THEN [Total Revenue]-[Total Cost]

END

Example #3: Tableau CASE when multiple conditions

Lets take example of grouping country into group1 and group2 for region, in this case you can use tableau CASE when multiple conditions as show below example.

We are trying to group region Asia and North America countries into 2 group

CASE [Region]
 WHEN "Asia" THEN
          (CASE [Country]
            WHEN "Bangladesh" THEN "Group1"
            WHEN "China" THEN "Group1"
            WHEN "Malaysia" THEN "Group1"
            WHEN "Myanmar" THEN "Group1"
            WHEN "Indonesia" THEN "Group1"
            ELSE "Group2"
          END)
 WHEN "North America" THEN
          (CASE [Country]
            WHEN "Canada" THEN "Group1"
            WHEN "Greenland" THEN "Group1"
            ELSE "Group2"
          END)
END

Below will be the output look like in table for in tableau

output for CASE in tableau with multiple conditions

Example #4: CASE function in tableau for parameter

Lets create one Parameter for date (year) to select year option from drop down value (parameter as string as shown below image)

Parameter for date (year) to select year option from drop down

Now create calculated field using Case function in Tableau as shown below:

CASE [year]

     WHEN '2014' THEN "Profit for 2014 was good"

     WHEN '2015' THEN "Profit for 2015 was minimum"

     WHEN '2016' THEN "Profit for 2014 was good"

     WHEN '2017' THEN "Profit for 2014 performed well"

END

Example #5: CASE function to compare numeric field

Lets take example, if you want to compare some numeric values using case, like you want to show profit and loss for Sales channels for Online and offline sales with different range of Total profit number.

Then you can use both Case and If condition in Tableau, as shown below formula. We are trying to show

  • If sales channel is Offline and total profit greater then 2000 the return profit else loss
  • If sales channel is Online and total profit greater then 1000 the return profit else loss
CASE [Sales Channel]

    WHEN "offline" THEN

             (IF[Total Profit] > 2000 THEN "profit" ELSE "Loss" END)

    WHEN "Online" THEN

          (IF [Total Profit] > 1000 THEN "profit" ELSE "Loss" END)
END

Example #6: CASE function in tableau with IF statement

CASE statement with an IF statement in Tableau:

IF [Sales] > 1000 THEN
  CASE [Region]
    WHEN "North" THEN "High Sales, North Region"
    WHEN "South" THEN "High Sales, South Region"
ELSE "High Sales, Other Region"
  END
ELSE
  CASE [Region]
     WHEN "North" THEN "Low Sales, North Region"
     WHEN "South" THEN "Low Sales, South Region"
ELSE "Low Sales, Other Region"
  END
END

In this example, the IF statement tests if the sales value is greater than 1000. If it is, the nested CASE statement will evaluate the region and return the result “High Sales, North Region”, “High Sales, South Region”, or “High Sales, Other Region”. If the sales value is not greater than 1000, the nested CASE statement will return the result “Low Sales, North Region”, “Low Sales, South Region”, or “Low Sales, Other Region”.

Example #7: Case Function in Tableau with dates Field

You can also apply the tableau Case statement in date field , however you need to convert the date into string in CASE statement as shown below formula.

CASE str(Year(([Date of Birth]))) 
      WHEN '1958' THEN "Born in 50s"  
      WHEN '1990' THEN "Born in 90s" 
END

IN above example you can see that we have extracted year from [Date of Birth] field and converted into String Using STR function, Because CASE always check the logics based on String data type not on date data type

Conclusion

With above tutorial you understand the different scenario where you can use CASE function in Tableau and also how to mix CASE and if condition both to return the values. IF you want to process your data fast then it is recommended to use CASE function in Tableau and if you are using to do the simple condition formula the IF statement is okay to go and use for your Tableau calculation.