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

Multiple case statements in Advanced Formula?

  • March 6, 2018
  • 6 replies
  • 333 views
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?
    Best answer by darkknight
    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"

    6 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"
    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.

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

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

        P
        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

        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. 
         

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

        Saved me again!

          Terms & ConditionsAccessibility statement