4 Replies Latest reply on Apr 20, 2017 3:35 PM by michaelm4269

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

    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

        • 1. Re: find max & min values in a field filtered by a range of dates
          Jaymo

          You could use a relationship or a subsummary report. I'm going to tell you the subsummary method because it seems most appropriate. Create a new layout and add a subsummary when sorted by Company name field. There is no need for a body part. Add the Company Name field and summary fields that get the minimum and maximum of the price field. Perform a find for any date range you desire, switch to the report layout, sort by Company Name and preview. If you want to see the report in browse mode, make sure you are showing records in list view.

          • 2. Re: find max & min values in a field filtered by a range of dates
            philmodjunk

            To find the min closing price for the current record's Ticket over the last 12 months can be done via either a relationship, ExecuteSQL or a query that does a find for 12 months of records of the same ticket and sorts them--all in a hidden window.

             

            All work from the fact that all the records will have the same ticket text and a Trading date that is either the current date - 12 months or the trading date - 12 months (choose an option here, either works).

             

            A calculation field named cYearAgo can compute:

             

            Date ( Month ( TradingDate ) - 12 ; Day ( TradingDate ) ; Year ( Tradingdate ) )

             

            To compute a value 12 months in the past. The date function will adjust correctly for negative month values so this works even though the month will usually be less than 12. Substitue the current date for Trading date and make the calculation unstored to get a date 12 months ago from today.

             

            Then this self join relationship will match to all records for the same ticket over the specified date:

             

            TradingDate:

            Prices::Ticket = PricesLastYear::Ticket AND

            Prices::TradingDate >= PricesLastYear::TradingDate AND

            Prices::YearAgo <= PricesLastYear::tradingDate

             

            CurrentDate:

            Prices::Ticket = PricesLastYear::Ticket AND

            Prices::YearAgo <= PricesLastYear::tradingDate

             

            Then Min ( PricesLastYear::ClosingPrice ) will return the lowest closing price over the past 12 months. Use the max function in the same format and context to get the highest price.

             

            ExecuteSQL would use a WHERE clause with similar inequalities and a max or min function to get the high and low prices.

             

            A scritped find would use similar values as find criteria and then sort the resulting records into ascending or descending order. (Or use summary fields to get max and min prices.)

            • 3. Re: find max & min values in a field filtered by a range of dates
              michaelm4269

              Hi

              Thanks so much for your help. Will incorporate your suggestion and let you

              know how it goes,

              Best Regards

              Michael

              • 4. Re: find max & min values in a field filtered by a range of dates
                michaelm4269

                HI Jaymo,

                 

                Thanks for you assistance and will give it a go and report back..

                Best Regards

                 

                Michael..