How to use CASE Statements in Calculated Columns in Spotfire

[ninja-popup ID=927] Click Here to download the Spotfire template shown in this post[/ninja-popup]

In the previous blog post, we learned how to use the OVER expression to find the peak oil production for a given month. In this post, we’ll start with the same dataset, but show how to use CASE statements in your custom expressions.

What is a CASE Statement?
A CASE statement is simply a variation of the standard if-then-else expression that’s common in Excel and some programming languages. Most people are familiar with writing IF statements, especially if you’re an Excel nerd like me. CASE statements are very similar to IF statements, but in my opinion, much more logical and intuitive to write and read.

CASE vs IF
In Spotfire, when would you use CASE instead of IF? The simple answer: when you start getting confused with the number of “ifs”, or scenarios. My rule of thumb is this: When there’s only one scenario being evaluated (if this: do this, else: do that), I write an IF statement. For all other situations (multiple ifs), then I’ll write a CASE statement. This is because “IF” statements are fairy simple when you’re evaluating two scenarios, but beyond that I prefer to use CASE.

Example Expressions with IF
For example, to determine if a column value (represented by X in the following expressions) is positive or negative, we could write the following expression:

IF(X >0, “Positive”, “Negative”)

  • Interpretation: If X is greater than 0, then return the word “Positive”, otherwise (i.e. for all other conditions), return the word “Negative”.

This is fairly straightforward, but it’s incorrectly evaluating when when X=0 by assigning the word “Negative”. Since 0 is neither positive nor negative, we need to modify the expression to account for scenarios where X=0:

IF(X >0, “Positive”, IF(X <0, “Negative”, “Zero”))

  • Interpretation : If X is greater than 0, then return the word “Positive”, If X is less than 0 then return the word “Negative”, otherwise (i.e. when X=0), return the word “Zero”.

This is a nested IF statement – multiple IF functions within one expression. As you can see, when you start evaluating multiple scenarios, it becomes a little more complicated. This can get confusing, especially in the future when you come back and try to interpret this expression. Imagine if you had even more, say 10, IF scenarios!

Example Expressions with CASE
The way to simplify this is to instead write a CASE statement. Think of CASE statements as IF statements that are easier to read and write.

     CASE
     When X > 0 Then “Positive”
     When X < 0 Then “Negative”
     Else “Zero”
     END 

Case statements always follow this syntax: Case – When – Then – Else – End (else is an optional argument), or all of the words in Blue above. The line breaks don’t matter – everything can go on one line or multiple lines. I like spacing it out because it’s easier to read.

Grouping Numerical Values into Categories
Let’s look at another example. Let’s say that our oil company has a monthly oil production target of 30,000 BBLS for each well. We can use color on a visualization to quickly identify which months met or exceeded the targets, which months fell short, and which months came close. We can accomplish by writing a CASE statement and represent numerical values categorically on a visualization such as a Bar Chart, Pie Chart, or even in a table, as shown in the screenshot below.

1 - Spotfire visualizations with color categories from CASE statement

To create the visualizations above, click Insert > Calculated Column. In the expression window, type the following expression:

     CASE
     WHEN [Oil] >= 30000 Then “Target Met”
     WHEN ([Oil]<30000) AND ([Oil]>=20000) Then “Target Missed by <1,000 BBLS”
     WHEN [Oil]<20000 Then “Target Missed by >1,000 BBLS”
     END

You can change the pink text in quotes to modify the row values, which are the custom category names. Give the column a name – I’ve named it Oil Target Indicator. After inserting the calculated column, use it on the Color By axis selectors, shown in the legend. Then change the colors of the different categories by clicking on the dots in the legend. In my coloring scheme, Green bars indicates months that exceeded the 30,000 BBL target, Yellow bars came within 1,000 BBLS of the target, and Red bars missed the target by more than 1,000 BBLS.

In the example above, I’ve filtered to 1 well. If we filter to multiple wells, and trellis by Well Name, we can see an individual graph for each well, and whether or not it’s met our 30,000 BBL production target, as shown below.

2 - Spotfire visualizations with color categories from CASE statement with Trellis

Hopefully this post has demonstrated the benefits of CASE Statements in Custom Expressions and sparked some ideas for other uses!

If you have any questions or comments, feel free to email me at kyle@datafuel.co, or leave a comment below.

[ninja-popup ID=927] Click Here to download the Spotfire template shown in the screenshots[/ninja-popup]

3 thoughts on “How to use CASE Statements in Calculated Columns in Spotfire

  1. Deepthi Reply

    Hi DF Thank you for the example.

    Can you Pls let me know if it possible to have multiple variables as output in a case statement, something like this FRef:

    case when ‘${ddl}’= “PLO” then ‘ABC’ and ‘XYZ
    else ‘NA’
    end

  2. Anonymous Reply

    @Deepthi
    You can’t have multiple outputs in the same node

Leave a Reply

Your email address will not be published. Required fields are marked *