3 Replies Latest reply on Feb 24, 2016 8:06 PM by bigtom

# Finding the Sum of non-empty fields for Statistical Linear Regression

Hello everyone,

I am developing a model to look at business income by month and want to attempt to apply a trend analysis to predict the next months income. I have a test database I will share.

The table contains 2 important fields - the amount and the period. The amount contains the income during the specific period. I have other calculation fields that are used in the final linear regression formula.

Y Intercept = b =  ( ( Sum X^2 ) ( Sum Y ) - (Sum X ) ( Sum XY ) )  /  n ( Sum X ^ 2 ) - ( Sum X ) ^ 2 )

Slope = m = n ( ( Sum of X*Y ) - ( Sum of X ) * ( Sum of Y ) ) / n ( Sum of X^2 ) - ( Sum of X ) ^ 2

X Intercept = Y = m * x + b

Here the x intercept would be plotted on a graph as the trend line compared to the amount.

Period (X)       1      2      3      4      5      6

Amount (Y)     4      4      3      4      8      10

X Intercept      2   3.3     4.7   6.1   7.4    8.8

The problem I have is if I add a record with an empty amount :

Period (X)       1      2      3      4      5      6      7

Amount (Y)     4      4      3      4      8      10

X Intercept      2   3.3     4.7   6.1   7.4    8.8   4

What I want :

If you see in the example file, the graph does not work with an additional blank field, which is what I need because I want to visualize the graph for the future. This is because I am making a summery of certain fields that now contain non-zero information, specifically :

forecast_sum_x  =  Total of Period

forecast_n           =  count of ID

I can create a related table and use a calculation to find these values (which is how I came up with the 3rd graph above), but then I cannot apply the trend to a found set - it is using the entire set of field information from the self-join relationship.

So, I need to be able to summarize the fields that are not empty for forecast_n and forecast_sum_x in order for it to work properly, and it doesn't appear I can use a related table for that information, unless I am missing something.

Any ideas would greatly be appreciated.

Thanks!

Jared

• ###### 1. Re: Finding the Sum of non-empty fields for Statistical Linear Regression

Just took a quick look. Things to consider:

ExecuteSQL

The limits of FileMaker charting.

• ###### 2. Re: Finding the Sum of non-empty fields for Statistical Linear Regression

Is ExecuteSQL limited by a found set? Eventually I want to only compare income on a month-by-month basis. My initial impression of Execute SQL is that it would apply the calculation to the entire set and not to the specific set I want to analyze. I'm probably mistaken though. I'll need to look further into it.

• ###### 3. Re: Finding the Sum of non-empty fields for Statistical Linear Regression

ExecuteSQL is independent of a found set but you can use it to fill in data in a separate table quickly. ExecuteQSL essentially can define its own found set in the query.

Another idea that comes to mind is having a special record generated that will always be added the found set and forced to sort to the last record and that can hold your predicted values. The record could be deleted after the comparison is done. If there is no value it will not be on the chart.

Charting with Filemaker's native tools is not always the best. I know many people use web viewers and Javascript charting because it has more flexibility. I avoid the FM native chart tool. It is slow and clunky.