Trouble writing a line expression with linest_m

[mhassinger]

I have a line chart that plots sales actuals and forecasts over month-year. I am having a problem getting a trend line for actuals because not every month has data yet, so whenever I'm looking at the current year, the trend nose dives because we've only got data for Jan-May.

I am trying to do this with y=mx+b using linest_m and linest_b. For example, here's the part of the expression for getting the slope.

=linest_m(

{$<PERIOD_YEAR={">$(=max(PERIOD_YEAR)-3)<=$(=max(PERIOD_YEAR))"}>}

total

aggr(sum({$<PERIOD_YEAR={">$(=max(PERIOD_YEAR)-3)<=$(=max(PERIOD_YEAR))"}>} SALES),PERIOD_YEARMONTH),PERIOD_YEARMONTH)

If I select 2010, this works fine, returning a positive slope that I can use to draw the line. If I select 2011, I get a negative slope, even though the sales have been trending up.

Any ideas on how to limit the expression so it's only calculating based on periods that have sales data for current year?

[johnw · Accepted solution]

Here's a solution from the attached example:

linest_m(total aggr(if(sum(Sales),sum(Sales)),Month),Month)*Month +
linest_b(total aggr(if(sum(Sales),sum(Sales)),Month),Month)

Basically, make sure you get null instead of 0 for the missing months.  Repeating your set expressions that many times would get annoying.  You could use variables perhaps.

[maksim_senin]

Hi,

I'm looking for information on forecasting with use of trend lines, found this (as well as many other topics) and still have a question - what linear trend is correct, built-in one or calculated via linest_m() and linest_b() functions?

Please take a look to the screen-short below - built-in linear trend does not match with caluculated by Jhon, what the reason? The functions do not properly work with non-numeris x-values, but as far as I can see all the values in the model are numberic, so I'm just stuck...