4 Replies Latest reply on Mar 30, 2016 9:27 AM by RPeters

    Trouble with Calculation ("and" "or" "xor")

    RPeters

      Hi Everyone,

       

      I’m having some difficulty with a calculation.  I’m keeping track of donations by individuals and each individual has a status.  I’m looking for a group of individuals who donated $500+ in 2015, and also donated $500+ in any other past year.  Their status should read “Donated in 2015, has donated in the past”.

       

      This is the calculation I’m using but it doesn’t seem to work.  It’s part of a larger case, and the case has been working perfectly so far.  This is my first glitch.

       

      Case (

       

      Donation 2015  ≥  500

       

      and

       

      Donation 2014 ≥ 500

      or

      Donation 2013 ≥ 500

      or

      Donation 2012 ≥ 500

      or

      Donation 2011 ≥ 500

      or

      Donation 2010 ≥ 500

      or

      Donation 2009 ≥ 500

      or

      Donation 2008 ≥ 500

      or

      Donation 2007 ≥ 500 ;

       

      "Donated in 2015, has donated in the past" ;

       

      and then the case continues on from there.  It’s giving the “Donated in 2015, has donated in the past” status to people who haven’t donated in 2015.  I just can’t quite wrap my mind around what I've done incorrectly.

       

      I’m pretty new to Filemaker, so any help would be very much appreciated!

       

      Thanks!!

      -Rachael

        • 1. Re: Trouble with Calculation ("and" "or" "xor")
          erolst

          AND binds more closely than OR (has a higher precedence), so your calculation actually is evaluated like this:

           

          ( Donation 2015  ≥  500

          AND

          Donation 2014 ≥ 500 )

          or

          Donation 2013 ≥ 500 or

          Donation 2012 ≥ 500 or

          Donation 2011 ≥ 500 or

          Donation 2010 ≥ 500 or

          Donation 2009 ≥ 500 or

          Donation 2008 ≥ 500 or

          Donation 2007 ≥ 500


          You need to override the default precedence using parentheses:


          Donation 2015  ≥  500

          AND

          (

          Donation 2014 ≥ 500 or

          Donation 2013 ≥ 500 or

          Donation 2012 ≥ 500 or

          Donation 2011 ≥ 500 or

          Donation 2010 ≥ 500 or

          Donation 2009 ≥ 500 or

          Donation 2008 ≥ 500 or

          Donation 2007 ≥ 500

          )

           

          If this part of something larger, I suggest you get familiar with the Let() function (unless you're already using it); then you can do stuff like this:

           

          Let ( [

            theAmount = 500 ;

            donatedIn2015 = Donation 2015 ≥ theAmount ;

            donatedBefore =

              Donation 2014 ≥ theAmount or

              Donation 2013 ≥ theAmount or

              Donation 2012 ≥ theAmount or

              Donation 2011 ≥ theAmount or

              Donation 2010 ≥ theAmount or

              Donation 2009 ≥ theAmount or

              Donation 2008 ≥ theAmount or

              Donation 2007 ≥ theAmount ;

              qualifiedDonor = donatedIn2015 AND donatedBefore ;

              { optionally calculate more variables ]

            ] ;

            use variables here, e.g. qualifiedDonor

          )

           

          One advantage is the improved legibility; another thing is that you could modify the qualifying amount simply in one place to change the calculation.

           

          Finally, note that if you'd store the annual amounts in a related table, you could calculate the qualification for each record (as a Boolean value 1 or 0), then do:


          Sum ( annualDonationsWithoutCurrentYear::amountQualifies) > 0


          without ever changing the calculation (which you will have to do annually with your existing structure).

          • 2. Re: Trouble with Calculation ("and" "or" "xor")
            schamblee

            Your case statement is close.

             

            Case (

             

            Donation 2015  ≥  500

            and

            Donation 2014 ≥ 500

            or

            Donation 2015  ≥  500

            and

            Donation 2013 ≥ 500

            or

            Donation 2015  ≥  500

            and

            Donation 2012 ≥ 500

            or

            Donation 2011 ≥ 500

            or

            Donation 2015  ≥  500

            and

            Donation 2010 ≥ 500

            or

            Donation 2015  ≥  500

            and

            Donation 2009 ≥ 500

            or

            Donation 2015  ≥  500

            and
            Donation 2008 ≥ 500

            or

            Donation 2015  ≥  500

            and

            Donation 2007 ≥ 500 ;

            "Donated in 2015, has donated in the past" ;
            )

            • 3. Re: Trouble with Calculation ("and" "or" "xor")
              user19752

              "donated $500+ in any other past year"


              Max ( put; all; past; years; here ) >= 500

              • 4. Re: Trouble with Calculation ("and" "or" "xor")
                RPeters

                Thanks so much everyone for the suggestions!  This is super helpful and the problem is solved!  Thank you thank you!!