3 Replies Latest reply on Nov 10, 2014 2:23 PM by JimMac

# Trend Analysis

### Title

Trend Analysis

### Post

Does FileMaker has any in-build or custom function that duplicates excel TREND function (link below)

http://office.microsoft.com/en-us/excel-help/trend-function-HP010062548.aspx

Thank you,

• ###### 1. Re: Trend Analysis

You have a bit of a problem with this TREND spreadsheet function as a Custom Function in FMP.  Although not impossible, it would be better solved using scripts or summary types.

The reason...

The Trend function requires x,y paired data and FMP cannot point to a series of records in a Custom Function.

Jim...

• ###### 2. Re: Trend Analysis

I am not sure how can I develop a script for it. I tried using following custom function [LinearTrend ( )] but got wrong results when compared to excel

http://www.fmfunctions.com/fid/135

I have sales data for 2011, 2012 and 2013 and based on those sales data I am trying to forecast 2014 (using simple linear trend)

• ###### 3. Re: Trend Analysis

I did a quick review of that referenced custom function.   The Author did a clever trick using Value Lists to build the X and Y data.  She had to parse and re-parse the data set to use the SUM and other data group functions in FMP.  If you note she had another custom function needed to work the main function.

http://www.fmcustomfunctions.com/fid/118

Both should work to do a Least Squares linear curve fit.  The function's output may be the problem.  It appears that the output is also a Value List that should have the A and B coefficients of the line function  y= A +Bx, with x=years and y=forecast.

Lets talk math a bit....

If you do Least squares on annual data you will get a value when x=0, y=A that may appear erroneous.  What I think you need is called Extrapolation.  I would read up using http://en.wikipedia.org/wiki/Extrapolation for linear extrapolation, which uses the same technique, but not Least Squares statistical analysis.  Since your X difference is always 1 year ( Quarterly forecasts would be 4 per year etc.).

Deciding on what best suits your forecast should be made with modeling using math to help what the eye can't see.  Al Gore used linear trends in 1998 to forecast Arctic temperature which failed miserably.

Lagrange method is short term best in most business models and add a bit of "Risk Analysis" in case your forecast fails.

A simple solution is just use the slope or change in Y over a year.  Average that change over 2 years or the tendency of the tendency.