6 Replies Latest reply on Jul 20, 2009 12:18 PM by comment_1

# Moving Average calculation

### Title

Moving Average calculation

### Post

I have a table with 2 fields: date and price. I would like to add "moving average" field for the price based on previous 5 days value.

I have tried self-join of the table, create a calculation field, but do not know how to define the query to locate previous 5 days price based on the date of each record.

Any pointers on how to accomplish this using FM 8 will be appreciated.

• ###### 1. Re: Moving Average calculation
You could define a calculation field (result is Date) =

Date - 4

and make the self-join:

YourTable:: cField ≤ YourTable 2:: Date
AND
YourTable:: Date ≥ YourTable 2:: Date

• ###### 2. Re: Moving Average calculation

Thank you for your prompt reply.  I understand the self-join setup. I am still confused in setting up the Moving Average calculation field.

In the following example, for each day, I need to calculate the average price of previous 4 days.  So, the MA need to lookup price field for each of the 5 days then average.

The problem is how to lookup the respective "Price" in the range "cfield" ... "date" ?

Table:

Date                 Price    cfield (=Date-4)            MA (=average of price from (date-4)… date)

1/5/2009          10        1/1/2009                      =average(10)

1/6/2009          11        1/2/2009                      =average(111+10)

1/7/2009          12        1/3/2009                      =average(12+11+10)

1/8/2009          13        1/4/2009                      =average(13+12+11+10)

1/9/2009          14        1/5/2009                      =average(14+13+12+11+10)

• ###### 3. Re: Moving Average calculation

jwong wrote:
So, the MA need to lookup price field for each of the 5 days then average.

Once you have the relationship in place, all you need is another calculation field (result is Number) =

Average (  YourTable 2:: Price )

• ###### 4. Re: Moving Average calculation

Uggh, stupid me...  I defined the relationship as "test" but setup the calculated field to average over "PDB:price" instead of over "test:price" ..

Thank you... Thank you... Thank you..

I concur you are a "Super Contributor"   ;-)

• ###### 5. Re: Moving Average calculation

One more question please if you allow...

Instead of using date as a range to calculate average, I wish to use the record number as a range. So, I setup two more self-joins named TEST2 and TEST3 respectively.

"average_2" is calculated as the average of the first n records which is ok. The "cRecNum" is also correct as each record is numbered sequentially.

However, "average_3" won't compute.  Is it because the matched field (cRecNum) is a computed field which is not stored unlike "Date" which is a user entered field?

--------------------------------------------------------------------------------------

Table  PDB

Price

Date

cDate = Date – 4

Average_1 = average(TEST::Price)

cRecNum = count (TEST2::Price)

cRecNum4 = cRecNum - 4

Average_2 = average (TEST2::Price)

Average_3 = average (TEST3::Price)

Date                 Price    cDate               cRecNum         cRecNum4

------------------------------------------------------------------------------

1/5/2009          10        1/1/2009          1                      -3

1/6/2009          11        1/2/2009          2                      -2

1/7/2009          12        1/3/2009          3                      -1

1/8/2009          13        1/4/2009          4                      0

1/9/2009          14        1/5/2009          5                      1

Self-Join           PDB -- TEST

cDate <= Date

AND     Date >= Date

Self-Join           PDB -- TEST2

Date >= Date

Self-Join           PDB -- TEST3

cRecNum4 <= cRecNum

AND     cRecNum >= cRecNum

• ###### 6. Re: Moving Average calculation

You cannot have an unstored calculation field on the child side of a relationship.

To calculate moving average of 5 consecutive records, based on the current found set and sort order, you don't need a relationship - you can use the GetNthRecord() function to get the previous values directly.

See, however, Bug when combining GetNthRecord about using aggregate functions in conjuction with GetNthRecord().