11 Replies Latest reply on May 31, 2013 8:29 AM by ninja

    Calculation based on Date Range

    JohhnyHilly

      Title

      Calculation based on Date Range

      Post

           I know there are a lot of threads out there relating to Date Range Calculations but I couldn't quite find the one to help me.

           I have a portal where each row is a new record that has a Date field and Number field on it. The calculation I need is just to subtract the Number field between two dates that will be selected. What is the best way to acheive this? I am aware that I will need 2 global fields, start date and end date but I am not sure how to link those global fields to the Date field in the portal so when selected will choose the Number values corresponding to those dates. How would I then write the calculation?

           Any help would be appreciated.

        • 1. Re: Calculation based on Date Range
          ninja

               Howdy,

               Could you be more clear as to the calc you are trying to do?

               I don't understand "subtract the number field between two dates...

               My imagination guesses you want the number subtracted from another value only if a third date field is between your global dates.

          • 2. Re: Calculation based on Date Range
            JohhnyHilly

                 Sorry, not too clear. Every day there will be a new record/row in the portal. Each record/row has a Date and a Number field. As an example, I want to subtract the value in the Number field on the 30th May by the value in the Number field on the 1st May. Clearer?

            • 3. Re: Calculation based on Date Range
              philmodjunk

                   But how will that work with a date range? When you specify a range of dates, you may get multiple values in the portal. How will FileMaker select from that list of values a single value to subtract?

              • 4. Re: Calculation based on Date Range
                JohhnyHilly

                     Ok sorry, again not clear enough. When I say date range I mean 2 dates. If the value in the Number field on the 30th May is 100 and the value in the Number field on the 1st May is 25, I want the calculation to do 100-25=75

                • 5. Re: Calculation based on Date Range
                  ninja

                       Will the two pertinent dates be the dates in your global fields?

                       If there are a number of dates, how will the dates of interest be selected?

                       If these are the dates in your global fields, you would make two table occurences, each liinked to a different global field (Global1 = TO1), (Global 2 = TO2) then use the calc in the form of

                       TO2::number - TO1::number

                  • 6. Re: Calculation based on Date Range
                    JohhnyHilly

                         How exactly do I link that global field to the relevant table?

                    • 7. Re: Calculation based on Date Range
                      ninja

                           Create a "Table Occurrence" (or T.O.) for each Global Link (if this is a new term, please say so). ....Relationshp Graph...double + sign at the bottom left.

                           Link each T.O. separately, one to each of your global fields.

                           MainTable::GlobalDate1  =  Gloabl1TO::Date

                           and separately

                           MainTable::GlobalDate2  =  Global2TO::Date

                           These table occurrences can be referenced separately in the caluclation definition using the Table Dropdown selector in the top left corner.

                      • 8. Re: Calculation based on Date Range
                        JohhnyHilly

                             Thanks Ninja, works well. I have another question now. How do I sum another field on that portal, between those two dates that I have already selected. The difference here, is that there are more records between those two dates that need to be included in the sum.

                        • 9. Re: Calculation based on Date Range
                          ninja

                               You may want to start a new thread...folks tend to look past threads marked as solved...

                               You can use yet another TO whose relationship is double defined.

                               "Records are related when"... Child::Date > Global1   AND  Child::Date < Global2

                               Then set a calculated field in your parent table which is Sum(ChlidTable::Number)   *Result is Number*

                          • 10. Re: Calculation based on Date Range
                            JohhnyHilly

                                 Thanks for that Ninja, again works well. This brings me to another question, again.

                                 The calculated field "Sum(ChildTable::Number)" you mentioned in your last post is located in the Parent table. Can you please tell me how I can create another calculation, that is still based on the dates between Global 1 and Global 2 but located in the Child table?

                            • 11. Re: Calculation based on Date Range
                              ninja

                                   Referencing my May22 post regarding a filtered Child TO using max date and Min date (Globals 1&2) as filter criteria

                                   Your calc field would simply be

                                   Sum ( FilteredChildTO::Number).

                                   If you sum referencing the old TO, you'll get the results through THAT filter...the Sum() function will give different results based on what filter it is looking through to the referenced Child TO.  Reference the TO that is using the filter (relationship definition) that you want the Sum() function to use.