1 2 Previous Next 20 Replies Latest reply on Dec 20, 2011 6:29 PM by RayCologon

    Partial Calculation Error


      I have a calculation field that references fields in the same table to determine if they have test scores across different periods of time (I only include the data of students that have the relative test scores over time for valid evaluation). Below is the calculation/logic, and it is only not working for students with GradeLevelFY11="0". The next line (for gradelevel=1) is working fine. Currently all students with gradelevelFY11="0" are showing a 0 in this field, even though I can manually look through and see that the majority of these students do actually have non-empty fields in both fields referenced. Any help would be much appreciated, and maybe I've looked at it too long and am missing something obvious...



      Student::GradeLevelFY11 = "0" and(IsEmpty(FY11_PSF_S) or IsEmpty(FY12_PSF_F));0; //For grade 0 students, if FY11_PSF_S OR FY12_PSF_F are blank, return a 0, else return 1

      Student::GradeLevelFY11 = "1" and(IsEmpty(FY11_NWF_CLS_S) or IsEmpty(FY12_DORF_CW_F));0;

      IsEmpty (FY11_DORF_CW_S) or IsEmpty( FY12_DORF_CW_F);0;1



      Thank you!

        • 1. Re: Partial Calculation Error

          Hi datarunner,


          Check field types: Text, Number?



          • 2. Re: Partial Calculation Error

            I verified all  fields involved (score fields as well as grade) are set to "number" and the output of the calculation field is "numeric".  I also tried taking the "" off from around the grade level (since it is a number field) but this didn't have an affect.  Still incorrect 0/1 indicator for grade 0.


            Thank you for the suggestion, I hadn't thought of that or checked it out.  Any other ideas?

            • 3. Re: Partial Calculation Error

              the calc looks perfectly legitimate, did you check in the field defintion if a previous value may be overwritten?

              • 4. Re: Partial Calculation Error



                I think you may have a logic issue.  Student::GradeLevelFY11 = "0" and(IsEmpty(FY11_PSF_S) or IsEmpty(FY12_PSF_F));0;   will return 0 if either FY11_PSF_S or FY12_PSF_F is empty


                I think what you want is:


                Student::GradeLevelFY11 = "0" and (IsEmpty(FY11_PSF_S) and IsEmpty(FY12_PSF_F));0;1


                This version should return 1 if either FY11_PSF_S or FY12_PSF_F has data in it and the Student::GradeLevelFY11 = "0".


                So that stated,  are you sure the second line is working correctly?


                Hope this helps

                Bruce Herbach

                • 5. Re: Partial Calculation Error



                  If I understand your formula, I believe you have written it incorrectly.  I would write it this way, putting the comment at the end, not in the middle of the formula.



                  Student::GradeLevelFY11 = 0 and IsEmpty(FY11_PSF_S) or

                  Student::GradeLevelFY11 = 0 and IsEmptyFY12_PSF_F) or

                  Student::GradeLevelFY11 = 1 and IsEmpty(FY11_NWF_CLS_S) or

                  Student::GradeLevelFY11 = 1 and FY12_DORF_CW_F);



                  IsEmpty (FY11_DORF_CW_S) or

                  IsEmpty( FY12_DORF_CW_F);







                  /* COMMENT: students w/GradeLevelFY11 = 0 and no value in either FY11_PSF_S or FY12_PSF_F receive value 0;

                  students w/GradeLevelFY11 = 1 and no value in either FY11_CLS_S or FY12_DORF_CW_F receive value 0;

                  all others receive a 1*/


                  or something like that. I write my comments in real words whenever possible and not abbreviations. [OFF NOTE: looks like fall, winter, spring test scores from an assesment tool. ;-)] and set them off at the bottom or top of the formula with several returns. I also prefer the /*comment */ notation over the //. Just seems to set them off better.


                  In your formula above, the 2nd statement following the first OR is only evaluating the IsEmpty(FY12_PSF_F) portion. It is not checking to see if the GradeLevelFY11 = 0 as the first statement does.


                  I may be wrong in the way I've read your formula, but I hope this helps.



                  • 6. Re: Partial Calculation Error

                    when combining OR with AND in such calculations, I also use more parens to make the calc clearer:


                    ( a=b ) and ( ( c=d ) or ( e=f ) )


                    -- sent from my iPhone4 --

                    Beverly Voth


                    • 7. Re: Partial Calculation Error

                      'datarunner' (what's you name?)


                      As a general note: unless you specifically want to test the text that was entered into a Number field it is generally better to use a numeric comparison, e.g. Student::GradeLevelFY11 = 0 rather than Student::GradeLevelFY11 = "0"


                      This shouldn't make any difference in this case - assuming each value entered into the student grade field is a single 0 character.




                      More specifically to your problem:


                      Is the calc evaluating in the correct context?


                      It looks like you are referencing fields from more than one TO (Student and the current TO) - is the relationship correct? IOW are you actually 'seeing' the Student record you think you are?


                      Failing all else, to find out what's going, on open up the data viewer (assuming you're using FM Advanced) and copy the calculation expression into a new entry in the Watch tab; now you can 'play with' the expression to try and narrow down the problem - for example get rid of the 'and' clause in the first line and just return 999 (for example) for all grade 0 students. If you don't see 999 when you navigate to a grade 0 record then you know that the initial test is failing - if you do see 999 then you know the problem is in the 'and' clause … vary and repeat as necessary. (If you're not using Advanced, you can do the same sort of thing, rather more laboriously, using either the existing calc field or a new one created solely for this purpose)






                      On 14 Dec 2011, at 20:39, datarunner wrote


                      I verified all  fields involved (score fields as well as grade) are set to "number"


                      • 8. Re: Partial Calculation Error
                        Stephen Huston

                        And be sure the "0" in the calculation definition is a Zero, not an OH!


                        I've seen it happen.

                        • 9. Re: Partial Calculation Error

                          I'm not finding anywhere that it's set not to be overwritten, and there are no priveledge sets defined in this DB (I'm the only user and use the DB to import and provide analysis).  It is also updating properly in this field for all other criteria in the calculation (other grade levels), so it wouldn't make sense to be a field setting I wouldn't think - or am I missing your point?

                          • 10. Re: Partial Calculation Error

                            Thanks Michelle,


                            I added the comments in the post but they aren't actually in my DB.  I will use your format to make it easier to read in the future.


                            I do not have "OR" statements in my calculation, I did not think this is needed in a case statement.  Am I missing something?


                            In reference to:

                            "In your formula above, the 2nd statement following the first OR is only evaluating the IsEmpty(FY12_PSF_F) portion. It is not checking to see if the GradeLevelFY11 = 0 as the first statement does."


                            Why wouldn't "GradeLevelFY11 = 0" be being evaluated when using case statement as I have?  Is my use of the case statement incorrect?

                            • 11. Re: Partial Calculation Error

                              Thanks Beverly,  I did have this formation originally but had the same issue.  I will try this again though, I've been through several iterations trying to figure out the issue and could have had other issues getting in the way when I was using your suggestion.

                              • 12. Re: Partial Calculation Error

                                Thanks Tom,


                                The two tables involved are "Student" and "DIBELs".  Student contains a StudentID as the PK, and DIBELs contains the same StudentID as it's FK (however the StudentIDs in DIBELs are also Unique, so it's a 1 to 1).  Let me know if you think this would be causing a problem (however it is working properly for the other gradelevels using the same structure).


                                Thanks for your suggestion, I'll break the calculation apart and evaluate to see if I can track it down. 


                                Datarunner (Leah )

                                • 13. Re: Partial Calculation Error

                                  Thanks Bruce,  that is how I want it evaluated.  I only want a "1" returned if neither of the fields are empty because I need to ensure the student took assessments during each assessment period before I put them in the pool of aggregated data to be analyzed.

                                  • 14. Re: Partial Calculation Error

                                    Thanks Stephen, I verified it is a zero and not an OH!

                                    1 2 Previous Next