2 Replies Latest reply on Jun 2, 2015 2:15 PM by CalvinCassens

    Average of annual averages (Moving Average)

    MauriceG

      Title

      Average of annual averages (Moving Average)

      Post

      Hello All,

      I have built a database to manage some investments. Included in the database is a summary layout that looks like this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                     

       

                     
                     

      Change from previous year

                     
                     

      Total at Dec. 31

                     
                     

      Annual Return

                     
                     

      3-year average return

                     
                     

      5-year average return

                     
                     

      10-year average return

                     
                     

      2004

                     
                     

      3

                     
                     

      33

                     
                     

      10%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2005

                     
                     

      5

                     
                     

      38

                     
                     

      15%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2006

                     
                     

      2

                     
                     

      40

                     
                     

      5.2%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2007

                     
                     

      4

                     
                     

      44

                     
                     

      10%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2008

                     
                     

      6

                     
                     

      50

                     
                     

      13.6%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2009

                     
                     

      5

                     
                     

      55

                     
                     

      10%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2010

                     
                     

      5

                     
                     

      60

                     
                     

      8.3%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2011

                     
                     

      6

                     
                     

      66

                     
                     

      10%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2012

                     
                     

      4

                     
                     

      70

                     
                     

      6%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2013

                     
                     

      7

                     
                     

      77

                     
                     

      10%

                     
                     

       

                     
                     

       

                     
                     

       

                     
                     

      2014

                     
                     

      3

                     
                     

      80

                     
                     

      3.75%

                     
                     

       

                     
                     

       

                     
                     

       

                     

       

      The Annual Return field is a calculation field in which the info comes from each year's record. I would like to create a 3, 5 and 10 year average return. For instance, the 3-year average return in 2014 would be the average of the annual returns for 2014, 2013 and 2012. In 2015, it would be the average of the annual returns for 2015, 2014 and 2013, etc. Same thing for the 5-year and 10-year average returns.

      Does anyone know how to do this? Do I need new tables? Would the fields for the 3-year, 5-year and 10-year average returns be calculation fields or statistics fields? New script?

      Thanks.

      Yvan

        • 1. Re: Average of annual averages (Moving Average)
          philmodjunk

          You can use multiple table occurrences of your existing table to set up "self join" relationships that match to records for the same investment for 3, 5 and 10 year intervals. Or you can use ExecuteSQL calculation fields to produce the same results if you are using FileMaker 12 or newer.

          Select one of these approaches and I'll describe it in more detail.

          Note: I am assuming that your table has data for more than one investment and you have a field in that table that uniquely identifies each different investment.

          • 2. Re: Average of annual averages (Moving Average)
            CalvinCassens

            Hello,

            I have a similiar issue, wanting to average only certain data from a field. Can you explain in more detail the best approach, using the original example?

            Thanks.