MovingFORECAST
Updated: 31 Oct 2012
Use the MovingFORECAST function to calculate the predicted value of y for a specific value of x for a series of x- and y-values within a resultant table or partition, without the need for a self-join. The intercept value is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[MovingFORECAST](
<@New_x, float,>
,<@Y, float,>
,<@X, float,>
,<@Offset, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
Arguments
@New_x
the specific x-value used to forecast the y-value. @New_x is an expression of type float or of a type that can be implicitly converted to float.
@Y
the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.
@X
the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.
@Offset
specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the MovingFORECAST calculation. @Id allows you to specify multiple MovingFORECAST calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· @RowNum must be in ascending order.
· To calculate the forecast from the first row of a dataset or of a partition of x- and y-values use the RunningFORECAST function.
· If @RowNum = 1 then MovingFORECAST is NULL.
· To calculate a single forecast value for a new x-value and a set of x- and y-values use the FORECAST function.
· Set @X to NULL to have the function maintain a constant set of x-values in the range 1 to window-size.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In this example we will store the monthly sales for three products: Leaf Blowers, Snow Blowers, and Pool Supplies. We will use the MovingFORECAST function to predict the new month’s sales based on the preceding 6 months historical sales. We will use the RANK() function to number the months, with the earliest month being assigned a rank of 1.
--Create the temporary table
CREATE TABLE #f(
EOM datetime,
item varchar(20),
sales money
)
--Populate the table with some data
INSERT INTO #f VALUES ('2010-10-31','Leaf Blowers',42548)
INSERT INTO #f VALUES ('2010-11-30','Leaf Blowers',77227)
INSERT INTO #f VALUES ('2010-12-31','Leaf Blowers',66944)
INSERT INTO #f VALUES ('2011-01-31','Leaf Blowers',34591)
INSERT INTO #f VALUES ('2011-02-28','Leaf Blowers',73468)
INSERT INTO #f VALUES ('2011-03-31','Leaf Blowers',50102)
INSERT INTO #f VALUES ('2011-04-30','Leaf Blowers',87270)
INSERT INTO #f VALUES ('2011-05-31','Leaf Blowers',51555)
INSERT INTO #f VALUES ('2011-06-30','Leaf Blowers',75139)
INSERT INTO #f VALUES ('2011-07-31','Leaf Blowers',50682)
INSERT INTO #f VALUES ('2011-08-31','Leaf Blowers',96577)
INSERT INTO #f VALUES ('2011-09-30','Leaf Blowers',77553)
INSERT INTO #f VALUES ('2011-10-31','Leaf Blowers',45299)
INSERT INTO #f VALUES ('2011-11-30','Leaf Blowers',71815)
INSERT INTO #f VALUES ('2011-12-31','Leaf Blowers',45070)
INSERT INTO #f VALUES ('2012-01-31','Leaf Blowers',60712)
INSERT INTO #f VALUES ('2012-02-29','Leaf Blowers',50021)
INSERT INTO #f VALUES ('2012-03-31','Leaf Blowers',38495)
INSERT INTO #f VALUES ('2012-04-30','Leaf Blowers',49125)
INSERT INTO #f VALUES ('2012-05-31','Leaf Blowers',49227)
INSERT INTO #f VALUES ('2012-06-30','Leaf Blowers',61511)
INSERT INTO #f VALUES ('2012-07-31','Leaf Blowers',66185)
INSERT INTO #f VALUES ('2012-08-31','Leaf Blowers',59871)
INSERT INTO #f VALUES ('2012-09-30','Leaf Blowers',69951)
INSERT INTO #f VALUES ('2012-10-31','Leaf Blowers',84861)
INSERT INTO #f VALUES ('2012-11-30','Leaf Blowers',79946)
INSERT INTO #f VALUES ('2010-10-31','Snow Blowers',77554)
INSERT INTO #f VALUES ('2010-11-30','Snow Blowers',89677)
INSERT INTO #f VALUES ('2010-12-31','Snow Blowers',75063)
INSERT INTO #f VALUES ('2011-01-31','Snow Blowers',57609)
INSERT INTO #f VALUES ('2011-02-28','Snow Blowers',65206)
INSERT INTO #f VALUES ('2011-03-31','Snow Blowers',50178)
INSERT INTO #f VALUES ('2011-04-30','Snow Blowers',41676)
INSERT INTO #f VALUES ('2011-05-31','Snow Blowers',50024)
INSERT INTO #f VALUES ('2011-06-30','Snow Blowers',35835)
INSERT INTO #f VALUES ('2011-07-31','Snow Blowers',71655)
INSERT INTO #f VALUES ('2011-08-31','Snow Blowers',69309)
INSERT INTO #f VALUES ('2011-09-30','Snow Blowers',50066)
INSERT INTO #f VALUES ('2011-10-31','Snow Blowers',77390)
INSERT INTO #f VALUES ('2011-11-30','Snow Blowers',58315)
INSERT INTO #f VALUES ('2011-12-31','Snow Blowers',83867)
INSERT INTO #f VALUES ('2012-01-31','Snow Blowers',92994)
INSERT INTO #f VALUES ('2012-02-29','Snow Blowers',67718)
INSERT INTO #f VALUES ('2012-03-31','Snow Blowers',79875)
INSERT INTO #f VALUES ('2012-04-30','Snow Blowers',30774)
INSERT INTO #f VALUES ('2012-05-31','Snow Blowers',33199)
INSERT INTO #f VALUES ('2012-06-30','Snow Blowers',33284)
INSERT INTO #f VALUES ('2012-07-31','Snow Blowers',30369)
INSERT INTO #f VALUES ('2012-08-31','Snow Blowers',50885)
INSERT INTO #f VALUES ('2012-09-30','Snow Blowers',81832)
INSERT INTO #f VALUES ('2012-10-31','Snow Blowers',72875)
INSERT INTO #f VALUES ('2012-11-30','Snow Blowers',56955)
INSERT INTO #f VALUES ('2010-10-31','Pool Supplies',67437)
INSERT INTO #f VALUES ('2010-11-30','Pool Supplies',67760)
INSERT INTO #f VALUES ('2010-12-31','Pool Supplies',36603)
INSERT INTO #f VALUES ('2011-01-31','Pool Supplies',67072)
INSERT INTO #f VALUES ('2011-02-28','Pool Supplies',71843)
INSERT INTO #f VALUES ('2011-03-31','Pool Supplies',67283)
INSERT INTO #f VALUES ('2011-04-30','Pool Supplies',62408)
INSERT INTO #f VALUES ('2011-05-31','Pool Supplies',57671)
INSERT INTO #f VALUES ('2011-06-30','Pool Supplies',95730)
INSERT INTO #f VALUES ('2011-07-31','Pool Supplies',58017)
INSERT INTO #f VALUES ('2011-08-31','Pool Supplies',88317)
INSERT INTO #f VALUES ('2011-09-30','Pool Supplies',63141)
INSERT INTO #f VALUES ('2011-10-31','Pool Supplies',43968)
INSERT INTO #f VALUES ('2011-11-30','Pool Supplies',60566)
INSERT INTO #f VALUES ('2011-12-31','Pool Supplies',33517)
INSERT INTO #f VALUES ('2012-01-31','Pool Supplies',37272)
INSERT INTO #f VALUES ('2012-02-29','Pool Supplies',76982)
INSERT INTO #f VALUES ('2012-03-31','Pool Supplies',43459)
INSERT INTO #f VALUES ('2012-04-30','Pool Supplies',66698)
INSERT INTO #f VALUES ('2012-05-31','Pool Supplies',76722)
INSERT INTO #f VALUES ('2012-06-30','Pool Supplies',88796)
INSERT INTO #f VALUES ('2012-07-31','Pool Supplies',53017)
INSERT INTO #f VALUES ('2012-08-31','Pool Supplies',93040)
INSERT INTO #f VALUES ('2012-09-30','Pool Supplies',78513)
INSERT INTO #f VALUES ('2012-10-31','Pool Supplies',45990)
INSERT INTO #f VALUES ('2012-11-30','Pool Supplies',72321)
--Calculate the monthly FORECAST for Leaf Blowers
SELECT cast(EOM as date) as EOM
,Item
,SALES
,CAST(wct.MovingFORECAST(RANK() OVER (ORDER BY EOM) + 1, sales, RANK() OVER (ORDER BY EOM),6,RANK() OVER (ORDER BY EOM),NULL) as money) as FORECAST
FROM #f
WHERE item = 'Leaf Blowers'
--Clean up
DROP TABLE #f
This produces the following result.
EOM Item SALES FORECAST
---------- -------------------- --------------------- ---------------------
2010-10-31 Leaf Blowers 42548.00 0.00
2010-11-30 Leaf Blowers 77227.00 111906.00
2010-12-31 Leaf Blowers 66944.00 86635.6667
2011-01-31 Leaf Blowers 34591.00 46789.00
2011-02-28 Leaf Blowers 73468.00 64716.80
2011-03-31 Leaf Blowers 50102.00 56894.00
2011-04-30 Leaf Blowers 87270.00 74084.2857
2011-05-31 Leaf Blowers 51555.00 60043.4286
2011-06-30 Leaf Blowers 75139.00 73054.8571
2011-07-31 Leaf Blowers 50682.00 67982.1429
2011-08-31 Leaf Blowers 96577.00 77592.7143
2011-09-30 Leaf Blowers 77553.00 84138.8571
2011-10-31 Leaf Blowers 45299.00 61656.5714
2011-11-30 Leaf Blowers 71815.00 70941.5714
2011-12-31 Leaf Blowers 45070.00 51845.1429
2012-01-31 Leaf Blowers 60712.00 52720.8571
2012-02-29 Leaf Blowers 50021.00 39066.8571
2012-03-31 Leaf Blowers 38495.00 38590.2857
2012-04-30 Leaf Blowers 49125.00 44332.2857
2012-05-31 Leaf Blowers 49227.00 40370.5714
2012-06-30 Leaf Blowers 61511.00 54231.1429
2012-07-31 Leaf Blowers 66185.00 60772.4286
2012-08-31 Leaf Blowers 59871.00 67393.00
2012-09-30 Leaf Blowers 69951.00 75311.8571
2012-10-31 Leaf Blowers 84861.00 83963.8571
2012-11-30 Leaf Blowers 79946.00 87739.2857
In this example, we will set @x to NULL and @new_x to 7 to generate a forecast for the 7 month using the preceding 6 months.
SELECT cast(EOM as date) as EOM
,Item
,SALES
,CAST(wct.MovingFORECAST(7, sales, NULL,6,RANK() OVER (ORDER BY EOM),NULL) as money) as FORECAST
FROM #f
WHERE item = 'Leaf Blowers'
This prodcues the following result.
EOM Item SALES FORECAST
---------- -------------------- --------------------- ---------------------
2010-10-31 Leaf Blowers 42548.00 NULL
2010-11-30 Leaf Blowers 77227.00 NULL
2010-12-31 Leaf Blowers 66944.00 NULL
2011-01-31 Leaf Blowers 34591.00 NULL
2011-02-28 Leaf Blowers 73468.00 NULL
2011-03-31 Leaf Blowers 50102.00 NULL
2011-04-30 Leaf Blowers 87270.00 70997.1429
2011-05-31 Leaf Blowers 51555.00 60788.1786
2011-06-30 Leaf Blowers 75139.00 70472.1786
2011-07-31 Leaf Blowers 50682.00 66086.8571
2011-08-31 Leaf Blowers 96577.00 75508.5714
2011-09-30 Leaf Blowers 77553.00 80564.0714
2011-10-31 Leaf Blowers 45299.00 63530.8214
2011-11-30 Leaf Blowers 71815.00 69942.6071
2011-12-31 Leaf Blowers 45070.00 55388.6786
2012-01-31 Leaf Blowers 60712.00 55530.2143
2012-02-29 Leaf Blowers 50021.00 45266.1071
2012-03-31 Leaf Blowers 38495.00 42834.3214
2012-04-30 Leaf Blowers 49125.00 46125.5357
2012-05-31 Leaf Blowers 49227.00 43294.5357
2012-06-30 Leaf Blowers 61511.00 53321.9643
2012-07-31 Leaf Blowers 66185.00 58982.0357
2012-08-31 Leaf Blowers 59871.00 63917.4286
2012-09-30 Leaf Blowers 69951.00 70568.3571
2012-10-31 Leaf Blowers 84861.00 78713.2857
2012-11-30 Leaf Blowers 79946.00 82645.6071
In this example we will look at the monthly sales and the sales forecast side-by-side for each of the three products.
SELECT cast(f1.EOM as date) as EOM
,f1.SALES as [LB]
,ROUND(wct.MovingFORECAST(7, f1.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f1.EOM),1), 0) as [FORECAST]
,f2.SALES as [SB]
,ROUND(wct.MovingFORECAST(7, f2.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f2.EOM),2), 0) as [FORECAST]
,f3.SALES as [PS]
,ROUND(wct.MovingFORECAST(7, f3.SALES, NULL, 6, ROW_NUMBER() OVER (ORDER BY f2.EOM),3), 0) as [FORECAST]
FROM #f f1
JOIN #f f2
ON f2.EOM = f1.EOM
AND f2.item = 'Snow Blowers'
JOIN #f f3
ON f3.EOM = f1.EOM
AND f3.Item = 'Pool Supplies'
WHERE f1.item = 'Leaf Blowers'
This produces the following result.
EOM LB FORECAST SB FORECAST PS FORECAST
---------- --------- --------- --------- --------- --------- ---------
2010-10-31 42548.00 NULL 77554.00 NULL 67437.00 NULL
2010-11-30 77227.00 NULL 89677.00 NULL 67760.00 NULL
2010-12-31 66944.00 NULL 75063.00 NULL 36603.00 NULL
2011-01-31 34591.00 NULL 57609.00 NULL 67072.00 NULL
2011-02-28 73468.00 NULL 65206.00 NULL 71843.00 NULL
2011-03-31 50102.00 NULL 50178.00 NULL 67283.00 NULL
2011-04-30 87270.00 70997.00 41676.00 44228.00 62408.00 64972.00
2011-05-31 51555.00 60788.00 50024.00 40651.00 57671.00 63829.00
2011-06-30 75139.00 70472.00 35835.00 36900.00 95730.00 81495.00
2011-07-31 50682.00 66087.00 71655.00 51374.00 58017.00 69753.00
2011-08-31 96577.00 75509.00 69309.00 60136.00 88317.00 78490.00
2011-09-30 77553.00 80564.00 50066.00 60881.00 63141.00 74624.00
2011-10-31 45299.00 63531.00 77390.00 71640.00 43968.00 61487.00
2011-11-30 71815.00 69943.00 58315.00 68199.00 60566.00 57161.00
2011-12-31 45070.00 55389.00 83867.00 77223.00 33517.00 39120.00
2012-01-31 60712.00 55530.00 92994.00 82805.00 37272.00 36284.00
2012-02-29 50021.00 45266.00 67718.00 80761.00 76982.00 47374.00
2012-03-31 38495.00 42834.00 79875.00 84114.00 43459.00 49524.00
2012-04-30 49125.00 46126.00 30774.00 58039.00 66698.00 60078.00
2012-05-31 49227.00 43295.00 33199.00 42965.00 76722.00 69426.00
2012-06-30 61511.00 53322.00 33284.00 27214.00 88796.00 85612.00
2012-07-31 66185.00 58982.00 30369.00 20093.00 53017.00 74434.00
2012-08-31 59871.00 63917.00 50885.00 30836.00 93040.00 80822.00
2012-09-30 69951.00 70568.00 81832.00 53238.00 78513.00 85836.00
2012-10-31 84861.00 78713.00 72875.00 73442.00 45990.00 66008.00
2012-11-30 79946.00 82646.00 56955.00 72976.00 72321.00 64773.00