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

# Calculation based on Date Range

### 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

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

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

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

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

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

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

• ###### 7. Re: Calculation based on Date Range

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.

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

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

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

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

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.