2 Replies Latest reply on Jan 18, 2011 1:20 PM by pdoak_1

    Calculating a percentage change



      Calculating a percentage change


      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.6
      3. Single user mode
      4. I have been using Filemaker Pro for a few of months

      I have a table which consists of the following fields:

      Date, AccountNo, Balance

      This table gets updated daily with the balance for each account.  I wish to create a report which calculates the percentage change in a client's account balance from a particular date, which I set in another table as a global variable, to the current date i.e. the latest record.  What is the best way of achieving that.  I am struggling with obtaining the account balance for each client based upon the global variable date.  Once I can do that the calculation is easy!

        • 1. Re: Calculating a percentage change

          Is it always a perfect match by date?

          If you enter 1/1/2011 in the global field, will there always be a record for that account with the same exact date or might it be a date that is a few days different? (In this example 1/1/2011 falls on Saturday, so the nearest date might be 1/3/2011--the following Monday.

          Either way you can set up a relationship to match by specified date and accountNo, but if there's any chance the specified date in the global field won't match to any record in your table, the relationship will need some modification in order for it to work.

          Assuming a perfect match by date:

          OtherTable::globalDate = Accounts::Date AND
          OtherTable::AccountNo = Accounts::AccountNo

          Then, if you enter 1/3/2011 into OtherTable::globaDate, Accounts::Balance will give you the balance on that date for the account specified in OtherTable::AccountNo.

          • 2. Re: Calculating a percentage change

            Yes, it will always be an exact match.  

            Thank you.  It worked.