Forecast Future Values
This Essbase calculation script example uses a linear regression forecasting method to produce a trend, or line, that starts with the known data values from selected previous months, and continues with forecasted values based on the known values.
The trend is calculated using @TREND. This example also shows how to check the results of the trend for “goodness of fit” to the known data values. In this case, the calculation script forecasts sales data for June–December, assuming that data currently exists only up to May.
Assume that the Measures dimension contains an additional child, ErrorLR, where the goodness-of-fit results are placed.
Example script:
Sales
(@TREND(@LIST(Jan,Mar,Apr),@LIST(1,3,4),,
@RANGE(ErrorLR,@LIST(Jan,Mar,Apr)),
@LIST(6,7,8,9,10,11,12),
Jun:Dec,LR););
The following table describes the parameters used in the forecasting calculation script:
Table 25-1 Parameters Used in the Example Calculation Script for Forecasting Future Values
Parameter | Description |
---|---|
@LIST(Jan,Mar,Apr) |
Represents the Ylist, or the members that contain the known data values. The @LIST function groups the three members as a comma-delimited list and keeps the list separate from other parameters. |
@LIST(1,3,4) |
Represents the Xlist, or the underlying variable values. Because Feb and May are skipped, Essbase numbers the Ylist values as 1,3,4. |
, |
The extra comma after the Xlist parameter indicates that a parameter (weightList) was skipped. This example uses a default weight of 1. |
@RANGE(ErrorLR,@LIST(Jan,Mar,Apr) |
Represents the errorList, or the member list where results of the goodness of fit of the trend line to Ylist are placed. The values placed in errorList are the differences between the data points in Ylist and the data points on the trend line that is produced. The @RANGE function combines the ErrorLR member with Ylist (Jan, Mar, Apr) to produce a member list. |
@LIST(6,7,8,9,10,11,12) |
Represents the XforecastList, or the underlying variable values for which the forecast is sought. This example forecasts values consecutively for Jun–Dec, so the values are 6,7,8,9,10,11,12. |
Jun:Dec |
Represents the YforecastList, or the member list into which the forecast values are placed. This example forecasts values for Jun–Dec, based on the values for Jan, Mar, and Apr. |
LR |
Specifies the Linear Regression method. |
Essbase cycles through the database, performing the following calculations:
-
Finds the known data values on which to base the trend (Sales for Jan, Mar, Apr), as specified by the Ylist and Xlist parameters.
-
Calculates the trend line using Linear Regression and places the results in Sales for Jun–Dec, as specified by the YforecastList parameter.
-
Calculates the goodness of fit of the trend line for the data values for Jan, Mar, and Apr, and places the results in ErrorLR for those months.
For example, the value in ErrorLR for Jan (4.57) means that after Essbase calculates the trend line, the difference between the Sales value for Jan (2339) and the Jan value on the trend line is 4.57. The ErrorLR values for Feb and May are #MISSING, because these months were not part of Ylist.
The results of the calculation script:
100 West Actual
Sales ErrorLR
Jan 2339 4.57
Feb 2298 #MI
Mar 2313 -13.71
Apr 2332 9.14
May 2351 #MI
Jun 2315.14 #MI
Jul 2311.29 #MI
Aug 2307.49 #MI
Sep 2303.57 #MI
Oct 2299.71 #MI
Nov 2295.86 #MI
Dec 2292 #MI