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

# Difficulty creating formula where CASE isn't working

### 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

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

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

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

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

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

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

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

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

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

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

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.