In this section: |
You can calculate trends in numeric data and predict values beyond the range of those stored in the data source by using the Forecast feature.
The calculations you can make to identify trends and forecast values are:
When predicting values in addition to calculating trends, Forecast continues the same calculations beyond the data points by using the generated trend values as new data points. For the linear regression technique, the calculated regression equation is used to derive trend and predicted values.
Forecast performs the calculations based on the data provided, but decisions about their use and reliability are the responsibility of the user. Therefore, Forecast predictions are not always reliable, and many factors determine how accurate a prediction will be.
Reference: |
You invoke Forecast processing by including Forecast in a Recap expression. In this command, you specify the parameters needed for generating estimated values, including the field to use in the calculations, the type of calculation to use, and the number of predictions to generate. The Recap field that contains the result of Forecast can be a new field (non-recursive) or the same field used in the Forecast calculations (recursive):
Forecast operates on the last Across field in the request. If the request does not contain an Across field, it operates on the last By field. The Forecast calculations start over when the highest-level sort field changes its value. In a request with multiple display commands, Forecast operates on the last Across field (or if there are no Across fields, the last By field) of the last display command. When using an Across field with Forecast, the field must be a summary field.
Note: Although you pass parameters to Forecast using an argument list in parentheses, Forecast is not a function. It can coexist with a function of the same name, as long as the function is not specified in a Recap expression.
ON TABLE SET STYLE *TYPE=DATA,COLUMN=MYFORECASTSORTFIELD,WHEN=FORECAST,COLOR=RED, $ENDSTYLE
The following are not supported with Forecast (within a Recap expression):
How to: Reference: |
A simple moving average is a series of arithmetic means calculated with a specified number of values from a field. Each new mean in the series is calculated by dropping the first value used in the prior calculation, and adding the next data value to the calculation.
Simple moving averages are sometimes used to analyze trends in stock prices over time. In this scenario, the average is calculated using a specified number of periods of stock prices. A disadvantage to this indicator is that because it drops the oldest values from the calculation as it moves on, it loses its memory over time. Also, mean values are distorted by extreme highs and lows, since this method gives equal weight to each point.
Predicted values beyond the range of the data values are calculated using a moving average that treats the calculated trend values as new data points.
The first complete moving average occurs at the n^{th} data point because the calculation requires n values. This is called the lag. The moving average values for the lag rows are calculated as follows: the first value in the moving average column is equal to the first data value, the second value in the moving average column is the average of the first two data values, and so on until the n^{th} row, at which point there are enough values to calculate the moving average with the number of values specified.
The Forecast dialog box opens.
If you select the same field as the By or Across field, this field will not appear in the output even if it is included in a display command.
The Forecast dialog box is shown in the following image.
The Forecast dialog box contains the following fields or options.
Field Setup
Double Exponential Average. Accounts for the tendency of data to either increase or decrease over time without repeating.
Exponential Average. Calculates a weighted average between the previously calculated value of the average and the next data point.
Linear Regression. Derives the coefficients of a straight line that best fits the data points and uses this linear equation to estimate values.
Moving Average. Calculates a series of arithmetic means using a specified number of values from a field.
Multivariate Regression. Predicts two or more dependent variables using one independent variable.
Triple Exponential Average. Accounts for the tendency of data to repeat itself in intervals over time.
Create a new procedure, open with Report Painter, and open the centord.mas file.
The Define dialog box opens.
The Expression Builder opens.
The Forecast dialog box opens.
The output is shown in the following image.
In the report, the number of generated values to use for the moving average is 3, and there are no REGION, QUANTITY, or LINE_COGS values for the generated PERIOD and MOVING_AVE fields.
Each MOVING_AVE field is computed by adding the three previous LINE_COGS values together and dividing the total by three. When moving into the future, where a LINE_COGS value is not available, the value of the last calculated MOVING_AVE is substituted for the missing LINE_COGS value. The calculations of the generated moving averages are explained here:
How to: Reference: |
The single exponential smoothing method calculates an average that allows you to choose weights to apply to newer and older values.
The following formula determines the weight given to the newest value.
k = 2/(1+n)
where:
The next calculation of the exponential moving average (EMA) value is derived by the following formula:
EMA = (EMA * (1-k)) + (datavalue * k)
This means that the newest value from the data source is multiplied by the factor k and the current moving average is multiplied by the factor (1-k). These quantities are then summed to generate the new EMA.
Note: When the data values are exhausted, the last data value in the sort group is used as the next data value.
The Forecast dialog box opens.
If you select the same field as the By or Across field, this field will not appear even if it is included in a display command.
The Forecast dialog box is shown in the following image.
The Forecast dialog box contains the following fields or options.
Double Exponential Average. Accounts for the tendency of data to either increase or decrease over time without repeating.
Exponential Average. Calculates a weighted average between the previously calculated value of the average and the next data point.
Linear Regression. Derives the coefficients of a straight line that best fits the data points and uses this linear equation to estimate values.
Moving Average. Calculates a series of arithmetic means using a specified number of values from a field.
Multivariate Regression. Predicts two or more dependent variables using one independent variable.
Triple Exponential Average. Accounts for the tendency of data to repeat itself in intervals over time.
Create a new procedure, open with Report Painter, and open the centord.mas file.
The Define dialog box opens.
The Expression Builder opens.
The Forecast dialog box opens.
When the report is run, the forecasted single exponential values will appear in the EXP_AVE column.
The output is shown in the following image.
In the report, the number of values used for each single exponential average is 3, and there are no REGION, QUANTITY, or LINE_COGS values for the generated PERIOD and EXP_AVE fields.
How to: Reference: |
Double exponential smoothing produces an exponential moving average that takes into account the tendency of data to either increase or decrease over time without repeating. This is accomplished by using two equations with two constants.
DOUBLEXP(t) = k * datavalue(t) + (1-k) * ((DOUBLEXP(t-1) + b(t-1))
b(t) = g * (DOUBLEXP(t)-DOUBLEXP(t-1)) + (1 - g) * (b(t-1))
These two equations are solved to derive the smoothed average. The first smoothed average is set to the first data value. The first trend component is set to zero. For choosing the two constants, the best results are usually obtained by minimizing the mean-squared error (MSE) between the data values and the calculated averages. You may need to use nonlinear optimization techniques to find the optimal constants.
The equation used for forecasting beyond the data points with double exponential smoothing is
forecast(t+m) = DOUBLEXP(t) + m * b(t)
where:
The Forecast dialog box opens.
If you select the same field as the By or Across field, this field will not appear even if it is included in a display command.
The Forecast dialog box contains the following fields or options:
Field Setup
Forecast Functions Setup
Double Exponential Average. Accounts for the tendency of data to either increase or decrease over time without repeating.
Exponential Average. Calculates a weighted average between the previously calculated value of the average and the next data point.
Linear Regression. Derives the coefficients of a straight line that best fits the data points and uses this linear equation to estimate values.
Moving Average. Calculates a series of arithmetic means using a specified number of values from a field.
Multivariate Regression. Predicts two or more dependent variables using one independent variable.
Triple Exponential Average. Accounts for the tendency of data to repeat itself in intervals over time.
Create a new procedure, open with Report Painter, and open the centord.mas file.
The Define dialog box opens.
The Expression Builder opens.
The Forecast dialog box opens.
When the report is run, the forecasted double exponential values will appear in the DBL_EXP_AVE column.
The output is shown in the following image.
In the report, the number of values used for each double exponential average is 3, and there are no REGION, QUANTITY, or LINE_COGS values for the generated PERIOD and DBL_EXP_AVE fields.
How to: Reference: |
Triple exponential smoothing produces an exponential moving average that takes into account the tendency of data to repeat itself in intervals over time. For example, sales data that is growing and in which 25% of sales always occur during December contains both trend and seasonality. Triple exponential smoothing takes both the trend and seasonality into account by using three equations with three constants.
For triple exponential smoothing you, need to know the number of data points in each time period (designated as L in the following equations). To account for the seasonality, a seasonal index is calculated. The data is divided by the prior season index and then used in calculating the smoothed average.
SEASONAL(t) = k * (datavalue(t)/I(t-L)) + (1-k) * (SEASONAL(t-1) + b(t-1))
b(t) = g * (SEASONAL(t)-SEASONAL(t-1)) + (1-g) * (b(t-1))
I(t) = p * (datavalue(t)/SEASONAL(t)) + (1 - p) * I(t-L)
These equations are solved to derive the triple smoothed average. The first smoothed average is set to the first data value. Initial values for the seasonality factors are calculated based on the maximum number of full periods of data in the data source, while the initial trend is calculated based on two periods of data. These values are calculated with the following steps:
b(0) = (1/L) ((y(L+1)-y(1))/L + (y(L+2)-y(2))/L + ... + (y(2L) - y(L))/L )
A(j) = ( y((j-1)L+1) + y((j-1)L+2) + ... + y(jL) ) / L
I(n) = ( y(n)/A(1) + y(L+n)/A(2) + ... + y((N-1)L+n)/A(N) ) / N
The three constants must be chosen carefully. The best results are usually obtained by choosing the constants to minimize the mean-squared error (MSE) between the data values and the calculated averages. Varying the values of npoint1 and npoint2 affect the results, and some values may produce a better approximation. To search for a better approximation, you may want to find values that minimize the MSE.
The equation used to forecast beyond the last data point with triple exponential smoothing is:
forecast(t+m) = (SEASONAL(t) + m * b(t)) / I(t-L+MOD(m/L))
where:
The Forecast dialog box opens.
If you select the same field as the By or Across field, this field will not appear even if it is included in a display command.
The Forecast dialog box is shown in the following image.
The Forecast dialog box contains the following fields or options:
Field Setup
Forecast Functions Setup
Double Exponential Average. Accounts for the tendency of data to either increase or decrease over time without repeating.
Exponential Average. Calculates a weighted average between the previously calculated value of the average and the next data point.
Linear Regression. Derives the coefficients of a straight line that best fits the data points and uses this linear equation to estimate values.
Moving Average. Calculates a series of arithmetic means using a specified number of values from a field.
Multivariate Regression. Predicts two or more dependent variables using one independent variable.
Triple Exponential Average. Accounts for the tendency of data to repeat itself in intervals over time.
Create a new procedure, open with Report Painter, and open the centord.mas file.
The Define dialog box opens.
The Expression Builder opens.
The Forecast dialog box opens.
When the report is run, the forecasted triple exponential values will appear in the TRPL_EXP_AVE column.
The output is shown in the following image.
In the report, the number of values used for each triple exponential average is 3, and there are no REGION, QUANTITY, or LINE_COGS values for the generated PERIOD and TRPL_EXP_AVE fields.
How to: Reference: |
The Linear Regression Equation estimates values by assuming that the dependent variable (the new calculated values) and the independent variable (the sort field values) are related by a function that represents a straight line:
y = mx + b
where:
REGRESS uses a technique called Ordinary Least Squares to calculate values for m and b that minimize the sum of the squared differences between the data and the resulting line.
The following formulas show how m and b are calculated.
where:
Trend values, as well as predicted values, are calculated using the regression line equation.
The Forecast dialog box opens.
If you select the same field as the By or Across field, this field will not appear even if it is included in a display command.
The Forecast dialog box is shown in the following image.
The Forecast dialog box contains the following fields or options:
Field Setup
Forecast Functions Setup
Double Exponential Average. Accounts for the tendency of data to either increase or decrease over time without repeating.
Exponential Average. Calculates a weighted average between the previously calculated value of the average and the next data point.
Linear Regression. Derives the coefficients of a straight line that best fits the data points and uses this linear equation to estimate values.
Moving Average. Calculates a series of arithmetic means using a specified number of values from a field.
Multivariate Regression. Predicts two or more dependent variables using one independent variable.
Triple Exponential Average. Accounts for the tendency of data to repeat itself in intervals over time.
Create a new procedure, open with Report Painter, and open the centord.mas file.
The Define dialog box opens.
The Define dialog box closes and you return back to Report Painter.
The Expression Builder opens.
The Forecast dialog box opens.
When the report is run, the forecasted linear regression values will appear in the LINEAR_REG column.
The output is shown in the following image.
In the report, the number of values used for each linear regression value is 3, and there are no REGION, QUANTITY, or LINE_COGS values for the generated PERIOD and LINEAR_REG fields.
You can use Forecast multiple times in one request. However, all Forecast requests must specify the same sort field, interval, and number of predictions. The only things that can change are the Recap field, method, field used to calculate the Forecast values, and number of points to average. If you change any of the other parameters, the new parameters are ignored.
If you want to move a Forecast column in the report output, use an empty Compute expression for the Forecast field as a placeholder. The data type (I, F, P, D) must be the same in the Compute expression and the Recap expression.
To make the report output easier to interpret, you can create a field that indicates whether the Forecast value in each row is a predicted value. To do this, define a virtual field whose value is a constant other than zero. Rows in the report output that represent actual records in the data source will appear with this constant. Rows that represent predicted values will display zero. You can also propagate this field to a HOLD file.
This example calculates moving averages and exponential averages for both the DOLLARS and BUDDOLLARS fields in the GGSALES data source. The sort field, interval, and number of predictions are the same for all of the calculations.
DEFINE FILE GGSALES SDATE/YYM = DATE; SYEAR/Y = SDATE; SMONTH/M = SDATE; PERIOD/I2 = SMONTH; END TABLE FILE GGSALES SUM DOLLARS AS 'DOLLARS' BUDDOLLARS AS 'BUDGET' BY CATEGORY NOPRINT BY PERIOD AS 'PER' WHERE SYEAR EQ 97 AND CATEGORY EQ 'Coffee' ON PERIOD RECAP DOLMOVAVE/D10.1= FORECAST(DOLLARS,1,0,'MOVAVE',3); ON PERIOD RECAP DOLEXPAVE/D10.1= FORECAST(DOLLARS,1,0,'EXPAVE',4); ON PERIOD RECAP BUDMOVAVE/D10.1 = FORECAST(BUDDOLLARS,1,0,'MOVAVE',3); ON PERIOD RECAP BUDEXPAVE/D10.1 = FORECAST(BUDDOLLARS,1,0,'EXPAVE',4); END
The output is shown in the following image.
PER DOLLARS BUDGET DOLMOVAVE DOLEXPAVE BUDMOVAVE BUDEXPAVE 1 801123 801375 801,123.0 801,123.0 801,375.0 801,375.0 2 682340 725117 741,731.5 753,609.8 763,246.0 770,871.8 3 765078 810367 749,513.7 758,197.1 778,953.0 786,669.9 4 691274 717688 712,897.3 731,427.8 751,057.3 759,077.1 5 720444 739999 725,598.7 727,034.3 756,018.0 751,445.9 6 742457 742586 718,058.3 733,203.4 733,424.3 747,901.9 7 747253 773136 736,718.0 738,823.2 751,907.0 757,995.6 8 655896 685170 715,202.0 705,652.3 733,630.7 728,865.3 9 730317 753760 711,155.3 715,518.2 737,355.3 738,823.2 10 724412 709397 703,541.7 719,075.7 716,109.0 727,052.7 11 620264 630452 691,664.3 679,551.0 697,869.7 688,412.4 12 762328 718837 702,334.7 712,661.8 686,228.7 700,582.3
The following example places the DOLLARS field after the MOVAVE field by using an empty COMPUTE command as a placeholder for the MOVAVE field. Both the COMPUTE command and the RECAP command specify formats for MOVAVE (of the same data type), but the format of the RECAP command takes precedence.
DEFINE FILE GGSALES SDATE/YYM = DATE; SYEAR/Y = SDATE; SMONTH/M = SDATE; PERIOD/I2 = SMONTH; END TABLE FILE GGSALES SUM UNITS COMPUTE MOVAVE/D10.2 = ; DOLLARS BY CATEGORY BY PERIOD WHERE SYEAR EQ 97 AND CATEGORY EQ 'Coffee' ON PERIOD RECAP MOVAVE/D10.1= FORECAST(DOLLARS,1,3,'MOVAVE',3); END
The output is shown in the following image.
Category PERIOD Unit Sales MOVAVE Dollar SalesCoffee 1 61666 801,123.0 801123 2 54870 741,731.5 682340 3 61608 749,513.7 765078 4 57050 712,897.3 691274 5 59229 725,598.7 720444 6 58466 718,058.3 742457 7 60771 736,718.0 747253 8 54633 715,202.0 655896 9 57829 711,155.3 730317 10 57012 703,541.7 724412 11 51110 691,664.3 620264 12 58981 702,334.7 762328 13 0 694,975.6 0 14 0 719,879.4 0 15 0 705,729.9 0
In the following example, the DATA_ROW virtual field has the value 1 for each row in the data source. It has the value zero for the predicted rows. The PREDICT field is calculated as YES for predicted rows, and NO for rows containing data.
DEFINE FILE CAR DATA_ROW/I1 = 1; END TABLE FILE CAR PRINT DATA_ROW COMPUTE PREDICT/A3 = IF DATA_ROW EQ 1 THEN 'NO' ELSE 'YES' ; MPG BY DEALER_COST WHERE MPG GE 20 ON DEALER_COST RECAP FORMPG/D12.2=FORECAST(MPG,1000,3,'REGRESS'); ON DEALER_COST RECAP MPG =FORECAST(MPG,1000,3,'REGRESS'); END
The output is:
DEALER_COST DATA_ROW PREDICT MPG FORMPG 2,886 1 NO 27.00 25.65 4,292 1 NO 25.00 23.91 4,631 1 NO 21.00 23.49 4,915 1 NO 21.00 23.14 5,063 1 NO 23.00 22.95 5,660 1 NO 21.00 22.21 1 NO 21.00 22.21 5,800 1 NO 24.20 22.04 6,000 1 NO 24.20 21.79 7,000 0 YES 20.56 20.56 8,000 0 YES 19.32 19.32 9,000 0 YES 18.08 18.08
WebFOCUS |