7 Replies Latest reply on Jul 11, 2013 4:05 PM by Malcolm

    Calculating Dates

    Jason_Farnsworth

      I would like to preform the following calculation but am not sure of the syntax

       

      If ( DateA is greater than or equal to ( DateB less 14 days ) ; 1 ; 10 )

       

      I am getting stuck DateB - the 14 days

       

      Thanks in advance,

       

      Jason Farnsworth

      Midland, TX

        • 1. Re: Calculating Dates
          Mike_Mitchell

          If ( DateA >= ( DateB - 14 ) ; 1 ; 10 )

           

          Or am I missing something in your question?

           

          Mike

          • 2. Re: Calculating Dates
            Jason_Farnsworth

            Mike

             

            I think the problem is I am missing something,

             

            Would (DateB - 14) subtract 14 days in a number calculation?

             

            This calculation requires a bolean result 1 = true 0 and others = False

             

            Jason

            • 3. Re: Calculating Dates
              pmconaway

              Jason, (DateB - 14) would indeed return a number but

               

              if (dateA >= (DateB - 14);1;10)  the DateA >= (DateB -14) that should return a true or false value (1 or 0) do you have Filemaker Pro advanced? You can use the data viewer to see what your results are while you are trying to build the correct calculation.

               

              Paul

              • 4. Re: Calculating Dates
                Mike_Mitchell

                Jason -

                 

                What exactly are you trying to accomplish with your calculation? In words?

                 

                Mike

                • 5. Re: Calculating Dates
                  Jason_Farnsworth

                  Mike,

                   

                  It is a filter for a portal

                   

                  Only show dates that are 14 days before DateA

                   

                  I think I have worked around it by using a separate calculation field based on a date calculation and using an IF statement which gives me the boolean result.

                   

                  Then I pointed the filter to that separate calculation field with again an IF statement

                   

                  It is working ok, but I am not sure if that was the cleanest way to go about it.

                   

                  Jason Farnsworth

                  Midland, TX

                  • 6. Re: Calculating Dates
                    erolst

                    Hi Jason,

                     

                    to chime in: your description makes me think that you're working with the data on the backend (the table in the portal). Unless I'm misunderstanding your goal, wouldn't it be easier to define a global calculation on the front-end, like myGlobal = [ dateA - 14 ], and use this as a match field for dateA in your relationship?

                     

                    This way you could also factor out the parameters and write a calculation like myGlobal = [ myDate +/- myOffset ], where myDate and myOffset are two other globals. This lets you perform all sorts of filtered queries by simpy changing the parameters.

                    • 7. Re: Calculating Dates
                      Malcolm

                      It is a filter for a portal

                      Only show dates that are 14 days before DateA

                       

                      In that case, one of these equations should do the trick.

                       

                      DateB  = DateA - 14 // shows dateB exactly 14 days before dateA

                      DateB  <=  DateA - 14  // shows all dateB 14 days or older than dateA

                       

                      If you provide an IF statement to do this one of the results must be true and the other false. For all practical purposes in Filemaker that is a one or a zero.  The IF statement that you provided had two positive results. The answer would be 1 or 10. Both of those numbers evaluate to true.

                       

                      malcolm