This example uses a linear regression forecasting method to produce a trend (@TREND), or line, that starts with the known data values from selected previous months and continues with forecasted values based on the known values, and 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););
Table 89 describes the parameters used in the forecasting calculation script:
Table 89. Parameters Used in the Example Calculation Script for Forecasting Future Values
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