3 Replies Latest reply on Apr 11, 2011 3:15 PM by philmodjunk

    3 Year Average Transactions

    DanielKarp

      Title

      3 Year Average Transactions

      Post

      Version: FM 10

      Does anyone know how to create a field that calculates the average transaction amount over a three period? I'd like the field to calc not on a fixed date, but on three years since their last transaction.

      Is it stupid to create a field to contain this data? Would it be better to create a sub-summary report in a new layout?

      Thanks for your advice,

      DEK

        • 1. Re: 3 Year Average Transactions
          philmodjunk

          You can use a calculation with a relationship to compute the average. You can also do this in a summary report. A one row filtered portal in FileMaker 11 can also be used, but not in 10.

          The details of how you would use either approach depend on the structure of your table and how you need to use this average once you have calculated it.

          Say you have a table with a "category" field that identifies which group of records should be averaged. Say you have a "cYear3" field that computes the year with Year ( YourDateField) and a cYear1 field defined as cYear3 - 3.

          Then a self join relationship like this could be defined:

          YourTable::Category = YourTable3Year::Category AND
          YourTable::cYear3 < YourTable3Year::cYear1 and
          YourTable::cYear1 > YourTable3Year::cYear1

          YourTable3Year is a new table occurrence of YourTable.

          Then Average ( YourTable3Year::DatafieldToBeAveraged ) will compute the 3 year average.

          In a summary report you can create and "average of" summary field, perform a find for a specific 3 year date range and then you can sort your records by Category order to use a sub summary part with this same summary field to compute the 3 year average.

          • 2. Re: 3 Year Average Transactions
            DanielKarp

            PhilModJunk -

            Man, you are some kind of dark FM wizard. I'll give you that. I don't understand why it worked, but I followed your directions - just to see if I could do it - and it worked.

            However, the last part of your advice does not produce what I'm looking for, whereas I would like to find the transaction average over a three year period based on the record's transactions not a fixed date.

            For example:

            Record "A" has the following transactions:

            $25 on 1/1/2009

            $25 on 1/1/2010

            $25 on 1/1/2011

            Record "A" has a 3-year transaction average of $25.

            Record "B" has a the following transactions:

            $100 on 1/1/2008

            $25 on 1/1/2009

            $25 on 1/1/2010

            $25 on 1/1/2011

            Records "B" also has a 3-year transaction average of $25, as the last three years he gave $25/year. If I were looking for a 4-year or total transaction average, Record "B" would actually be $43.75.

            How do I get to where I need to be?

            Thanks for everything!

            DEK

            • 3. Re: 3 Year Average Transactions
              philmodjunk

              Each transaction should be in its own record. That's the assumption on which my suggested solution was based. The example data you've posted here would then represent 8 separate records.