9 Replies Latest reply on Oct 15, 2013 11:13 AM by philmodjunk

    Difficulty creating formula where CASE isn't working

    Jenim

      Title

      Difficulty creating formula where CASE isn't working

      Post

            

      We are trying to carry out the following

      We have specific fields to work with

      KSP

      SEN

      Baseline Eq

      End of KS

            

      We want to be able to use a calculation field called GRADE to work out the following (using CASE formula hasn’t worked)

      Choose 1 from 3 types of SEN - PMLD, SLD, SLDHA (text)

      If SEN = PMLD

      If KSP = 0; “Miss”;

      If KSP > 0.4; “Outstanding”;

      If KSP > 0.3; “Good”;

      “RImp”)

      ____________________

            

      For SEN = SLD it is more complicated

      If SEN = SLD

      If KSP = 0; “Miss”;

      If KSP > 0.6; “Outstanding”;

      If KSP > 0.5; “Good”;

      “RImp”)

            

      BUT

            

      If either Baseline Eq OR End of KS > 11

      then

      If SEN = SLD

      If KSP = 0; “Miss”;

      If KSP > 0.33; “Outstanding”;

      If KSP > 0.3; “Good”;

      “RImp”)

      ____________________

            

      For SEN = SLDHA

      If SEN = SLDHA

      If KSP = 0; “Miss”;

      If KSP > 0.8; “Outstanding”;

      If KSP > 0.7; “Good”;

      “RImp”)

            

      BUT

            

      If either Baseline Eq OR End of KS > 11

      then

      If SEN = SLD

      If KSP = 0; “Miss”;

      If KSP > 0.41; “Outstanding”;

      If KSP > 0.33; “Good”;

      “RImp”)

        • 1. Re: Difficulty creating formula where CASE isn't working
          philmodjunk

               Some of your info contradicts itself so my response provides more of a format of how to set this up than something that you can copy exactly. I am also assuming that "SLD", "SLDHA" etc is literal text and not the names of other fields.

               Instead of if and case functions, you may want to set up a table of related table and use it for a table look up. that way if the values for KSP need to be changed, you can edit the data in a table instead of redefining a complex calculation. But working with a calculation for now:

          If ( Baseline Eq > 11 OR End of KS > 11 ;     // I'm guessing as to what you mean by the reference to BaseLine Eq
                   Case ( SEN = "SLD" ; Case ( KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                         KSP > 0.3 ; "Good" ;
                                                         KSP > 0.33 ; "Outstanding" ;
                                                         "RImp" // negatives or values greater than 0 and less than or equal to 0.3
                                                        ) ; // inner Case
                             SEN = "SLDHA" ; Case ( KSP = 0 ; "Miss" ;  
                                                             KSP > 0.7 ; "Good" ;
                                                             KSP > 0.8 ; "Outstanding" ;
                                                             "RImp" // negatives or values greater than 0 and less than or equal to 0.7
                                                            ) ; // inner Case
                             ) ; // outer case, no results are specified if SEN = "PMLD" in this context
                 // Else
                   Case ( SEN = "PMLD" ; Case ( KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                           KSP > 0.3 ; "Good" ;
                                                           KSP > 0.4 ; "Outstanding" ;
                                                           "RImp" // negatives or values greater than 0 and less than or equal to 0.4
                                                          ) ; // inner Case
                           SEN = "SLD" ; Case ( KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                        KSP > 0.5 ; "Good" ;
                                                        KSP > 0.6 ; "Outstanding" ;
                                                        "RImp" // negatives or values greater than 0 and less than or equal to 0.4
                                                       ) // inner Case
                            ) // Outer Case
                 ) // If

                

               Please note the contradiction here:

                

          If either Baseline Eq OR End of KS > 11

          then

          If SEN = SLD

          If KSP = 0; “Miss”;

          If KSP > 0.33; “Outstanding”;

          If KSP > 0.3; “Good”;

          “RImp”)

                

          If either Baseline Eq OR End of KS > 11

          then

          If SEN = SLD

          If KSP = 0; “Miss”;

          If KSP > 0.41; “Outstanding”;

          If KSP > 0.33; “Good”;

          “RImp”)

                

               You've specified two different sets of possible results for the exact same set of criteria.

          • 2. Re: Difficulty creating formula where CASE isn't working
            Jenim

                 Many many thanks.  I am unfamiliar with formula construction and have tried many times to work this out.

                 You were right about the contradiction.  It should have been 

                  

            We are trying to carry out the following

            We have specific fields to work with

            KSP

            SEN

            Baseline Eq

            End of KS

                  

            We want to be able to use a calculation field called GRADE to work out the following (using CASE formula hasn’t worked)

            Choose 1 from 3 types of SEN - PMLD, SLD, SLDHA (text)

            If SEN = PMLD

            If KSP = 0; “Miss”;

            If KSP > 0.4; “Outstanding”;

            If KSP > 0.3; “Good”;

            “RImp”)

            ____________________

                  

            For SEN = SLD it is more complicated

            If SEN = SLD

            If KSP = 0; “Miss”;

            If KSP > 0.6; “Outstanding”;

            If KSP > 0.5; “Good”;

            “RImp”)

                  

            BUT

                  

            If either Baseline Eq OR End of KS > 11

            then

            If SEN = SLD

            If KSP = 0; “Miss”;

            If KSP > 0.33; “Outstanding”;

            If KSP > 0.3; “Good”;

            “RImp”)

            ____________________

                  

            For SEN = SLDHA

            If SEN = SLDHA

            If KSP = 0; “Miss”;

            If KSP > 0.8; “Outstanding”;

            If KSP > 0.7; “Good”;

            “RImp”)

                  

            BUT

                  

            If either Baseline Eq OR End of KS > 11

            then

            If SEN = SLDHA

            If KSP = 0; “Miss”;

            If KSP > 0.41; “Outstanding”;

            If KSP > 0.33; “Good”;

            “RImp”)

                  

            Does this help make a difference?

                  

            Best wishes.

            • 3. Re: Difficulty creating formula where CASE isn't working
              philmodjunk

                   You have exchanged one contradiction for another. Now the following (in red) contradicts:

              If either Baseline Eq OR End of KS > 11

              then

              If SEN = SLD

              If KSP = 0; “Miss”;

              If KSP > 0.33; “Outstanding”;

              If KSP > 0.3; “Good”;

              “RImp”)

              ____________________

                    

              For SEN = SLDHA

              If SEN = SLDHA

              If KSP = 0; “Miss”;

              If KSP > 0.8; “Outstanding”;

              If KSP > 0.7; “Good”;

              “RImp”)

                    

              BUT

                    

              If either Baseline Eq OR End of KS > 11

              then

              If SEN = SLDHA

              If KSP = 0; “Miss”;

              If KSP > 0.41; “Outstanding”;

              If KSP > 0.33; “Good”;

              “RImp”)

                    

                   I suggest organizing a table of criteria and results like you might find in a spreadsheet:

                    

                   With data arranged like this:

                    

                   Baseline Eq   End of KS    SEN        KSP          Result
                   >11                             "SLDHA"    0            "Miss"
                                           >11     "SLDHA"     0            "Miss"
                   <= 11                          "SLD"        0            "Miss"

                    

                   And so forth until you have documented all possible criteria and the specified result for each value or range of values.

                   From there, we can avoid the complex combination of nested functions and use a relationship to reference the desired text in the Result column.

              • 4. Re: Difficulty creating formula where CASE isn't working
                Jenim

                     Thanks again.

                     I have persevered with as formula for now.

                     This is it.

                      

                     If ( Art_and_design_EQ > 11  or Art_and_design_E_EQ  > 11;
                     Case ( SEN = "SLD"; Case ( Art_and_design KSP = 0; "Miss";
                     Art_and_design KSP > 0.3; "Good";
                     Art_and_design KSP > 0.33; "Outstanding";
                     "RImp" );
                     Case ( SEN = "SLDHA"; Case ( Art_and_design KSP = 0; "Miss";
                     Art_and_design KSP > 0.33; "Good";
                     Art_and_design KSP > 0.41; "Outstanding";
                     "RImp" );
                      
                     If ( Case ( SEN = "PMLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   
                     Art_and_design KSP > 0.3 ; "Good" ;
                     Art_and_design KSP > 0.4 ; "Outstanding" ;
                     "RImp" ); 
                     Case( SEN = "SLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   
                     Art_and_design KSP > 0.5 ; "Good" ;
                     Art_and_design KSP > 0.6 ; "Outstanding" ;
                     "RImp" );  
                     Case ( SEN = "SLDHA" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   
                     Art_and_design KSP > 0.7 ; "Good" ;
                     Art_and_design KSP > 0.8 ; "Outstanding" ;
                     "RImp" )
                      
                     Error message tells me I have either too many ( or ) or too few of ( or ).
                     Any suggestions?
                      
                     Best wishes.
                • 5. Re: Difficulty creating formula where CASE isn't working
                  philmodjunk

                       Note how I used indenting to make it easier to account for the right and left parenthesis. It's much harder to spot them when formatted like your last post.

                       A quick scan of just the first few rows shows many missing right parenthesis. Note the lines in my example such as:

                           ) // inner case

                       that's what appears to be missing from yours.

                        

                  • 6. Re: Difficulty creating formula where CASE isn't working
                    Jenim

                         I am still struggling.

                         Have tried this but still get error messages.

                         If ( Art_and_design_EQ > 11 OR Art_and_design_E_EQ > 11 ;     // I'm guessing as to what you mean by the reference to BaseLine Eq
                             Case ( SEN = "SLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                                   Art_and_design KSP > 0.3 ; "Good" ;
                                                                   Art_and_design KSP > 0.33 ; "Outstanding" ;
                                                                   "RImp" // negatives or values greater than 0 and less than or equal to 0.3
                                                                  ) ; // inner Case
                                       SEN = "SLDHA" ; Case ( Art_and_design KSP = 0 ; "Miss" ;  
                                                                       Art_and_design KSP > 0.33 ; "Good" ;
                                                                       Art_and_design KSP > 0.41 ; "Outstanding" ;
                                                                       "RImp" // negatives or values greater than 0 and less than or equal to 0.7
                                                                      ) ; // inner Case
                                       ) ; // outer case, no results are specified if SEN = "PMLD" in this context
                           // Else
                             Case ( SEN = "PMLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                                     Art_and_design KSP > 0.3 ; "Good" ;
                                                                     Art_and_design KSP > 0.4 ; "Outstanding" ;
                                                                     "RImp" // negatives or values greater than 0 and less than or equal to 0.4
                                                                    ) ; // inner Case
                                     SEN = "SLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                                  Art_and_design KSP > 0.5 ; "Good" ;
                                                                  Art_and_design KSP > 0.6 ; "Outstanding" ;
                                                                  "RImp"); // negatives or values greater than 0 and less than or equal to 0.4
                                                                 ) // inner Case 
                         Case ( SEN = "SLDHA"; Case ( Art_and_design KSP = 0; "Miss";
                         Art_and_design KSP  >  0.7; "Good;
                         Art_and_design KSP > 0.8; "Outstanding"
                         "RImp")
                                      ) // Outer Case
                           ) // If
                          

                          

                    • 7. Re: Difficulty creating formula where CASE isn't working
                      philmodjunk

                           The last portion of the expression would appear to need these modifications:

                           // Else

                               Case ( SEN = "PMLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                                       Art_and_design KSP > 0.3 ; "Good" ;
                                                                       Art_and_design KSP > 0.4 ; "Outstanding" ;
                                                                       "RImp" // negatives or values greater than 0 and less than or equal to 0.4
                                                                      ) ; // inner Case
                                       SEN = "SLD" ; Case ( Art_and_design KSP = 0 ; "Miss" ;   //nothing is specified for > 0 and < to 0.3
                                                                    Art_and_design KSP > 0.5 ; "Good" ;
                                                                    Art_and_design KSP > 0.6 ; "Outstanding" ;
                                                                    "RImp"); // negatives or values greater than 0 and less than or equal to 0.4
                                                                   ) ; // inner Case --> added missing semi-colon
                                       SEN = "SLDHA"; Case ( Art_and_design KSP = 0; "Miss"; --->removed extra case function
                                                                             Art_and_design KSP  >  0.7; "Good;
                                                                             Art_and_design KSP > 0.8; "Outstanding" ; ---> added missing semi-colon
                                                                             "RImp"
                                                                          ) // inner Case ---> removed ) from preceding line and put it here for better clarity
                                        ) // Outer Case
                             ) // If

                                Please note that you did not follow my advice to set up a table listing all criteria and the results associated with each. Such can be implemented in a much simpler fashion using a related table to return the desired result as well as enabling future updates of the various values and data ranges without having to redefine a calculation--thus making it possible for a user who is not a database developer to manage such updates.

                            

                      • 8. Re: Difficulty creating formula where CASE isn't working
                        Jenim

                             I am away from base today.  Many thanks for helping me as I am new to much of this.

                             I would need further help with the table to understand how it would look up the range of information needed.

                             There are so many elements 3 types of SEN - PMLD, SLD and SLDHA

                             Different success criteria depending on score being above or below 11; and a range of scores which is extensive.

                             if you have the patience, I would certainly appreciate your further help with it.

                             How would the field GRADE look up the information from this new table ?

                             Best wishes.

                        • 9. Re: Difficulty creating formula where CASE isn't working
                          philmodjunk
                               

                                    I would need further help with the table to understand how it would look up the range of information needed.

                               Which is why I asked you to post the info that I requested in table format to this thread. If you go back a few days, you'll find that I posted several rows in such a table as an example.

                               With that table of data posted, I can spell out the specifics of how such a look up would function. This approach would require more than one match field as each pair of match fields would handle part of the criteria that is currently part of your nested if and case functions. Match fields can match to specific values or to values that are in a range or that are greater than or less than the value in the corresponding match field so we actually have more than one way to set this up.

                               Also, do you need to copy the data into a field in your main table or just display it dynamically? If you copy the value, subsequent changes to data in the look up table will not change the copied data. If you display it dynamically, any such changes will automatically produce updates in what is displayed. Either approach can be the correct one depending on the needs of your project. Both rely on the same relationship, but use it in a different manner.