Skip to main content
darkknight
darkknight
Expert ⭐️
March 6, 2018
Solved

Multiple case statements in Advanced Formula?

I am trying to leverage an Advanced Formula in Data Management and where I have successfully used them before for IF-THEN-ELSE type scenarios, does anyone know if you can do IF-THEN-ELSIF-THEN-ELSE type scenarios?

I tried 

case when (X < Y and X is not null) then 0 else (X > Y and X < Z) then 50 else (X > Z) 100 end

That didn't work (neither did removing the brackets) so I'm thinking either my syntax is bad or this is not possible.  Any feedback?
    darkknight
    darkknightAnswer
    Expert ⭐️
    March 7, 2018
    Figured this out with a little help from Gainsight:

    case when X < Y then 0 when (X > Y and X < Z) then 50 else 100 end

    Looks like you go straight into a "when" statement after the first value option. Not need for a "then" 

    And just tie it off with an "else # end"
    Jeff Kirkpatrick
    View in thread

    7 replies

    darkknight
    darkknightAuthorAnswer
    Expert ⭐️
    March 7, 2018
    Figured this out with a little help from Gainsight:

    case when X < Y then 0 when (X > Y and X < Z) then 50 else 100 end

    Looks like you go straight into a "when" statement after the first value option. Not need for a "then" 

    And just tie it off with an "else # end"
    Translate
    Jeff Kirkpatrick
      jordan_cook
      jordan_cook
      Contributor ⭐️⭐️⭐️⭐️⭐️
      June 24, 2024

      Thanks for this post. I have done this once or twice before, but I couldn’t remember the syntax.

      Translate
      benwanlessmenlo
      benwanlessmenlo
      Contributor ⭐️⭐️⭐️⭐️⭐️
      February 25, 2025

      This post saves me every time I try to set up one of these Advanced Formula fields!

      Translate
        parimala_agnihotri
        Helper ⭐️
        September 2, 2025

        I have similar use case and it does not seem to work if I use two variables. Has anyone tested this - case when x=0, then A case when (x >0 and y <100) then B else 0 end

        Translate
        romihache
        romihache
        VIP ⭐️⭐️⭐️⭐️⭐️
        September 3, 2025

        I have similar use case and it does not seem to work if I use two variables. Has anyone tested this - case when x=0, then A case when (x >0 and y <100) then B else 0 end

        Hi ​@parimala_agnihotri, I'm not sure if you copied your formula exactly, but if you did, you'll just need to remove the comma. The rest of the syntax looks correct. 
         

        Translate
        Continuous improvement is better than delayed perfection
        benwanless
        benwanless
        Contributor ⭐️⭐️⭐️⭐️
        October 8, 2025

        Saved me again!

        Translate
          alizee
          alizee
          VIP ⭐️⭐️⭐️⭐️⭐️
          October 6, 2026

          Thanks for your post and self-solutioning! Super helfpul.

          Translate
          The most expensive part of building is the mistakes. That's true in construction. Not so much in CSOps. So ask questions, make mistakes and learn. All views expressed here are my own.
            Terms & ConditionsAccessibility statement