2

I'm trying to calculate a forecast of sales based on the 3 previous months which either can be actuals or forecast.

company_id    Year    Month    Actuals    Forecast 
  123456      2014      1         10     
  123456      2014      2         15     
  123456      2014      3         17     
  123456      2014      4                 14.00
  123456      2014      5                 15.33 
  123456      2014      6                 15.44
  123456      2014      7                 14.93

Month 4 = (10+15+17)/3
Month 5 = (15+17+14)/3
Month 6 = (17+14+15.33)/3
Month 7 = (14+15.33+15.44)/3

Let's say I want to calculate the forecast for the next 18 months for each company.

I'm looking at the data for last year. Some companies has e.g. 2 months of data and other 12 months and so on.

I have searched and found many different solutions but all of them only takes the actuals into account.

I think I have to make a recursive CTE, but I can't figure it out.

Please help :)

exASHacto
  • 23
  • 1
  • 4
  • Are all of your months ordered sequentially exactly like they are in your example? Ie,if n = any given month, is a month's calculation simply the actual/forecast values of (n-1) + (n-2) + (n-3) / 3? – WillardSolutions Feb 01 '16 at 17:33
  • 1
    What's your DBMS? Does it support *Windowed Aggregates*, `MIN... OVER (ORDER BY ...)`? – dnoeth Feb 01 '16 at 17:37
  • @EatPeanutButter - yea and no. The months are dates like 01-01-2015 and so on. I have just showed their row_number (if that makes sense). – exASHacto Feb 01 '16 at 17:44
  • 1
    @dnoeth - I'm using SSMS 2008 R2. Yes it supports OVER(ORDER BY ) etc – exASHacto Feb 01 '16 at 17:48
  • It looks like you can add actual and forecast to get your working number, since it's always one or the other populated. – Smandoli Feb 01 '16 at 17:57

1 Answers1

1

So you want moving average based on previous moving average :) I think that when programming on SQL Server one always have to realize which tasks are more suited to set-based approach and which are more general row-by-row approach. Your task, if you ask me, is perfect for simple row-by-row processing. Here's an example

declare @temp table ([Month] int, Actual decimal(29, 2), Forecast decimal(29, 2))
declare @month int

insert into @temp (
    [Month], Actual
)
select 1, 10 union all
select 2, 15 union all
select 3, 17

select @month = isnull(max([Month]) + 1, 1) from @temp

while @month <= 18
begin
    insert into @temp (
        [Month], Forecast
    )
    select
        @month, avg(a.value) as Forecast
    from (
        select top 3 isnull(Actual, Forecast) as value
        from @temp
        order by [Month] desc
    ) as a

    select @month = @month + 1
end

select * from @temp
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Great ! I'm getting closer and closer. I have tried to tweak the code so I can use it, but I need some more help. I will edit my question... I think the solution is near. – exASHacto Feb 02 '16 at 09:51
  • so you want to calculate till certain month or just 18 months for each company? Suppose one company has most recent data for 2014-03 and another for 2013-12, what should be in the results? – Roman Pekar Feb 02 '16 at 11:47
  • Let's say we start on 2014-01. If I only got data for 2014-03 and nothing more for the rest of the year, then 2015-01 = (11*0 + 10*1) / 12 = 0,83. 2015-02=(10*0 + 10*1 + 0.833*1) / 12 = 0,90. Does that make sense? – exASHacto Feb 02 '16 at 11:54
  • what I mean is - do you want the last month in the results to be the same for all companies? or should I just forecast 18 months for each company based on which actual data is in the table? – Roman Pekar Feb 02 '16 at 12:00
  • Ah okay. The forecast should be for each company. I have made a cursor that loop each company. Now I just need the average to be calculated correctly... – exASHacto Feb 02 '16 at 12:10