1 2 Previous Next 16 Replies Latest reply on Sep 16, 2015 5:26 PM by Mike_Mitchell

    Nested case statements - Please provide clarity

    lbl159

      Case ( S3_security description 1 = "Liquidation Preference" ;

      Case ( S3_Investments_Entities::S3_Financing Round Order 1 = S3_Investments_Entities::S3_Financing Round 1;

      Case ( S3_Liquidation Preference Value 1 ≥ S3_Total Liquidation Value; S3_Total Liquidation Value ;

      S3_Liquidation Preference Value 1 < S3_Total Liquidation Value ;

      S3_security description 1 = "Pari-Passu" ;

      S3_security description 1 = "Participating Preferred" ;

       

       

      I'm working on a very large calculation that has many parameters and many sub parameters to determine what to calculate. 

       

      I've created the shell of the formula and how I am going to lay it out. I don't have very much experience with FMP/Database/Coding work.  How does the syntax look so far? Any better suggestions than the way I am doing it?

       

      It's color coded because I'm doing it in Word to help keep track of all of the function parts when creating this system.

        • 1. Re: Nested case statements - Please provide clarity
          wimdecorte

          I think you are going down the wrong path.   I would use one Case() function call and make the tests more readable through the use of AND and OR, something like

           

          Case(

          condition1 and condition2 and condition3 ; result1;

          condition1 and condition2 and condition4 ; result2;

          ...

          )

          • 2. Re: Nested case statements - Please provide clarity
            lbl159

            I was thinking to do it this way to make it shorter and less muddy.  Do case functions not work well like this?

            • 3. Re: Nested case statements - Please provide clarity
              lbl159

              There are 3 items in tier 1, 10 items in tier 2, and maybe 5-6 items in tier 3

              • 4. Re: Nested case statements - Please provide clarity
                wimdecorte

                They'll work fine, but I can pretty much guarantee that you'll tear your hair out in a few months when you have to revisit it and try and understand the logic of the nesting.

                • 5. Re: Nested case statements - Please provide clarity
                  SteveMartino

                  I think if you write it out in words first, substituting your fields, the process may become easier. 

                  Then I would do it in the data viewer.  If you try to save or evaluate, it will immediately kick back syntax errors.

                  It's hard to help because I cant tell if that's all one statement nested, as it tests different fields in different ways.

                  • 6. Re: Nested case statements - Please provide clarity
                    lbl159

                    Green is the first Case. Blue is the second. Red is the third

                    • 7. Re: Nested case statements - Please provide clarity
                      SteveMartino

                      That doesn't help much as you also mentioned:

                      There are 3 items in tier 1, 10 items in tier 2, and maybe 5-6 items in tier 3

                      You have to state what  you want each test to do/return if it's true, and what each test should do/return if false, for each tier.  Once again I would write it in words first, using real world terms.

                      • 8. Re: Nested case statements - Please provide clarity
                        wimdecorte

                        You could also use a Let() function to wrap things and keep it easy to read and easy to troubleshoot:

                         

                        let(

                        [

                        _tier3_result = case(whatever) ;

                        _tier2_result = case(condition1 and _tier3_result ; result1 ;

                                                         condition2 and _tier3_result ; result2 ;

                                                        ...

                                                        );

                        _tier1_result = case()

                        ];

                         

                        _tier1_result

                         

                        )

                        • 9. Re: Nested case statements - Please provide clarity
                          Mike_Mitchell

                          +1 to the Let suggestion. Your field names are so long, I got vertigo trying to read your original calculation.   

                          • 10. Re: Nested case statements - Please provide clarity
                            lbl159

                            Awesome thanks for the suggestion.  I'm working on that formula right now. One question:

                            let(

                            [

                            _tier3_result = case(whatever) ;

                            _tier2_result = case(condition1 and _tier3_result ; result1 ;

                                                            condition2 and _tier3_result ; result2 ;

                             

                            In terms of the Case function, the bolded would just be another test of the Case function.  The ultimate calculation that is being done comes from the Tier 3 result.  Tier 1 and 2 are just parameters that lead to choosing that specific calculation.  Looking at this I would think it would be like:

                             

                            _tier2_result = case(condition1 ; _tier3_result ;

                                                            condition2 ; _tier3_result ;

                             

                            Am I confused?

                            • 11. Re: Nested case statements - Please provide clarity
                              Extensitech

                              Case (

                                   S3_security description 1 = "Liquidation Preference"

                                   ; Case (

                                        S3_Investments_Entities::S3_Financing Round Order 1 = S3_Investments_Entities::S3_Financing Round 1

                                        ; Case (

                                             S3_Liquidation Preference Value 1 >= S3_Total Liquidation Value

                                             ; S3_Total Liquidation Value

                               

                                             ;S3_Liquidation Preference Value 1 < S3_Total Liquidation Value

                                             ; S3_security description 1 = "Pari-Passu"

                               

                                             ; S3_security description 1 = "Participating Preferred"

                               

                                              ;...

                               

                              I know, I indent weird, but this helped me notice a few things...

                               

                              • If S3_security description 1 is anything other than "Liquidation Preference", the rest of the expression you have so far will be ignored.
                              • Your last two results (notably, not conditions) will therefore both return a Boolean false, since if we got this far, that field equals "Liquidation Preference"
                              • Your last two conditions, compare the same values, using >= and <, respectively. Those two conditions cover all the possibilities. There's no way you'll get to the "otherwise" result ("Participating Preferred")
                              • The three result parameters will return a number (S3_Total Liquidation Value), 0 (False) or 0 (False). What value are you actually trying to return?

                               

                              It appears that this function, as written thus far, would produce the same results as

                               

                              Case (

                                   S3_security description 1 = "Liquidation Preference"

                                   and S3_Investments_Entities::S3_Financing Round Order 1 = S3_Investments_Entities::S3_Financing Round 1

                                   and S3_Liquidation Preference Value 1 >= S3_Total Liquidation Value

                               

                                   ; S3_Total Liquidation Value

                               

                                   ; 0

                              )

                               

                              There are reasons to nest Case statements, but they are pretty rare compared to If. Once a true condition is met, the corresponding result is considered and the calc stops. You may already realize this, but I mention it because it's often an "aha" moment to users first getting the hang of Case.

                               

                              Also, I feel I should point out that if you have fields with numbers in them ("S3", "...Value 1" , "...Round 1"), in a relational database, it almost always indicates that you're missing a table. :-)

                               

                              You've already gotten some good responses, and I may be off base, but I hope this helps.

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: Nested case statements - Please provide clarity
                                erolst

                                Extensitech wrote:

                                Also, I feel I should point out that if you have fields with numbers in them ("S3", "...Value 1" , "...Round 1"), in a relational database, it almost always indicates that you're missing a table. :-)

                                 

                                Especially in light of this:

                                lbl159 wrote:

                                I'm working on a very large calculation that has many parameters and many sub parameters to determine what to calculate.

                                 

                                At a guess, an additional table (FinancingRounds?) would simplify this calculation immensely.

                                • 13. Re: Nested case statements - Please provide clarity
                                  wimdecorte

                                  Emphasizing what Erolst and Chris are saying: you may not want to do this in a monster calculation.  Think "workflow" and see if you can do this in a script that would set the result statically in a field instead of having an unstored calc that will be complex to troubleshoot and potentially would not give you any "point in time" data since it would calculate on the fly when needed

                                  • 14. Re: Nested case statements - Please provide clarity
                                    lbl159

                                    Thanks all! I have to say I am a big fan of this forum.  Even though my knowledge of FMP is rather limited, over the course of this job I have been able to do some great work.  Big thanks to all you guys.

                                    1 2 Previous Next