3 Replies Latest reply on Jan 13, 2011 10:15 AM by philmodjunk

    Calculation field summary incorrect



      Calculation field summary incorrect


      OK, here is another bonehead question:

      I have a calculation field that counts weekdays between two dates, when I try to add an average summary field based on this calc field, I get a negative weird number. Here is the calc for the days:

      7 * Int ( ( Medical Exam Received - Medical Exam Requested ) / 7 ) +Middle ( "0012345501234544012343340123223491111234010012340" ; 7 * (DayOfWeek ( Medical Exam Requested ) - 1 ) + DayOfWeek ( Medical Exam Received ) ; 1 )

      Any help here would of course be appreciated beyond belief.


        • 1. Re: Calculation field summary incorrect

          Perhaps Medical Exam Received is blank for one of the records in your found set. That would cause this calculation to evaluate as a very negative number and it would then skew your average in the negative direction by a large value.

          • 2. Re: Calculation field summary incorrect

            ... and I'm guessing that if more than 1 record is blank it would really mess it up eh?

            Can I get around this by adding a "if this is not blank" if staement to my days calc?

            • 3. Re: Calculation field summary incorrect

              Dates in FileMaker record the number of days since December 31, year 0000 and a blank date field in this calcuation evaluates as zero.

              What value is valid if a date field is blank? Do you want that to be null, 1, 0 or....? The answer to that question should guide you in how to modify the calculation so that it checks for empty date fields.

              Another alternative might be to perform a find that omits all records where this calculation evaluates to a number less than zero so that those records are excluded from the summary calculation.