AnsweredAssumed Answered

find max & min values in a field filtered by a range of dates

Question asked by michaelm4269 on Apr 20, 2017
Latest reply on Apr 20, 2017 by michaelm4269

Hi there,

I am relatively new to Filemaker and use it for some recording of equities (shares) – especially if they are not listed on major stock exchanges .

 

I have price file for the unlisted equities where I have recorded the closing price. My Filemaker 'price file' is a simple one:

 

Ticker(Text)

CompanyName(Text)
TradingDate(Date)

ClosePrice(Number)

Comment(Text)

LastAvailDate(Calculation)

FirstAvailDate(Calculation)

etc,..

 

A table showing each ticker (in the header) and its price & date (sorted) in columns, however to find the Highest & Lowest price recorded in the last 12 months needs a manual search.

 

Is there a simple way to have the 12-month Highest & Lowest values displayed on the layout without conducting a search on each ticker?

 

I thought of having a script run when the layout is entered, however depending on the volume of data being processed, the response time suffers. Can anyone help with a custom function please, I cant seem to adapt Brian Dunning’s Min Value (1st;Value) function easily without using it on all dates rather than just the last 12 months. - I don’t fully understand the approach.

 

I then have to copy & paste my results into another table in Filemaker.

 

Many thanks

 

Michael Mead

Outcomes