Calculating Trends and Predicting Values With FORECAST

Example: Calculating a New Simple Moving Average Column

This request defines an integer value named PERIOD to use as the independent variable for the moving average. It predicts three periods of values beyond the range of the retrieved data.

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 SMONTH/M = SDATE;
 PERIOD/I2 = SMONTH;
END
TABLE FILE GGSALES
  SUM UNITS DOLLARS
  BY  CATEGORY BY PERIOD
  WHERE SYEAR EQ 97 AND CATEGORY NE 'Gifts'
  ON PERIOD RECAP MOVAVE/D10.1= FORECAST(DOLLARS,1,3,'MOVAVE',3);
END

The output is:

In the report, the number of values to use in the average is 3 and there are no UNITS or DOLLARS values for the generated PERIOD values.

Each average (MOVAVE value) is computed using DOLLARS values where they exist. The calculation of the moving average begins in the following way:

• The first MOVAVE value (801,123.0) is equal to the first DOLLARS value.
• The second MOVAVE value (741,731.5) is the mean of DOLLARS values one and two: (801,123 + 682,340) /2.
• The third MOVAVE value (749,513.7) is the mean of DOLLARS values one through three: (801,123 + 682,340 + 765,078) / 3.
• The fourth MOVAVE value (712,897.3) is the mean of DOLLARS values two through four: (682,340 + 765,078 + 691,274) /3.

For predicted values beyond the supplied values, the calculated MOVAVE values are used as new data points to continue the moving average. The predicted MOVAVE values (starting with 694,975.6 for PERIOD 13) are calculated using the previous MOVAVE values as new data points. For example, the first predicted value (694,975.6) is the average of the data points from periods 11 and 12 (620,264 and 762,328) and the moving average for period 12 (702,334.7). The calculation is: 694,975 = (620,264 + 762,328 + 702,334.7)/3.

Example: Using an Existing Field as a Simple Moving Average Column

This request defines an integer value named PERIOD to use as the independent variable for the moving average. It predicts three periods of values beyond the range of the retrieved data. It uses the same name for the RECAP field as the first argument in the FORECAST parameter list. The trend values do not display in the report. The actual data values for DOLLARS are followed by the predicted values in the report column.

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 SMONTH/M = SDATE;
 PERIOD/I2 = SMONTH;
END
TABLE FILE GGSALES
  SUM UNITS DOLLARS
  BY  CATEGORY BY PERIOD
  WHERE SYEAR EQ 97 AND CATEGORY NE 'Gifts'
  ON PERIOD RECAP DOLLARS/D10.1 = FORECAST(DOLLARS,1,3,'MOVAVE',3);
END

The output is:

 

k = 2/(1+n)

EMA = (EMA * (1-k)) + (datavalue * k)

Example: Calculating a Single Exponential Smoothing Column

The following defines an integer value named PERIOD to use as the independent variable for the moving average. It predicts three periods of values beyond the range of retrieved data.

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 SMONTH/M = SDATE;
 PERIOD/I2 = SMONTH;
END
TABLE FILE GGSALES
  SUM UNITS DOLLARS
  BY  CATEGORY BY PERIOD
  WHERE SYEAR EQ 97 AND CATEGORY NE 'Gifts'
  ON PERIOD RECAP EXPAVE/D10.1= FORECAST(DOLLARS,1,3,'EXPAVE',3);
END

The output is:

In the report, three predicted values of EXPAVE are calculated within each value of CATEGORY. For values outside the range of the data, new PERIOD values are generated by adding the interval value (1) to the prior PERIOD value.

Each average (EXPAVE value) is computed using DOLLARS values where they exist. The calculation of the moving average begins in the following way:

• The first EXPAVE value (801,123.0) is the same as the first DOLLARS value.
• The second EXPAVE value (741,731.5) is calculated as follows. Note that because of rounding and the number of decimal places used, the value derived in this sample calculation varies slightly from the one displayed in the report output:

  n=3 (number used to calculate weights)

  k = 2/(1+n) = 2/4 = 0.5

  EXPAVE = (EXPAVE*(1-k))+(new-DOLLARS*k) = (801123*0.5) + (682340*0.50) = 400561.5 + 341170 = 741731.5

• The third EXPAVE value (753,404.8) is calculated as follows:

  EXPAVE = (EXPAVE*(1-k))+(new-DOLLARS*k) = (741731.5*0.5)+(765078*0.50) = 370865.75 + 382539 = 753404.75

For predicted values beyond those supplied, the last EXPAVE value is used as the new data point in the exponential smoothing calculation. The predicted EXPAVE values (starting with 706,741.6) are calculated using the previous average and the new data point. Because the previous average is also used as the new data point, the predicted values are always equal to the last trend value. For example, the previous average for period 13 is 706,741.6, and this is also used as the next data point. Therefore, the average is calculated as follows: (706,741.6 * 0.5) + (706,741.6 * 0.5) = 706,741.6

EXPAVE = (EXPAVE * (1-k)) + (new-DOLLARS * k) = (706741.6*0.5) +
         (706741.6*0.50) =  353370.8 + 353370.8 = 706741.6

forecast(t+m) = DOUBLEXP(t) + m * b(t)

Example: Calculating a Double Exponential Smoothing Column

The following defines an integer value named PERIOD to use as the independent variable for the moving average. The double exponential smoothing method estimates the trend in the data points better than the single smoothing method:

SET HISTOGRAM = OFF
TABLE FILE CENTSTMT
SUM ACTUAL_YTD
  BY PERIOD
  ON PERIOD RECAP EXP/D15.1 = FORECAST(ACTUAL_YTD,1,0,'EXPAVE',3);
  ON PERIOD RECAP DOUBLEXP/D15.1 = FORECAST(ACTUAL_YTD,1,0,
     'DOUBLEXP',3,3);
WHERE GL_ACCOUNT LIKE '3%%%'
END

The output is:

 

forecast(t+m) = (SEASONAL(t) + m * b(t)) / I(t-L+MOD(m/L))

Example: Calculating a Triple Exponential Smoothing Column

In the following, the data has seasonality but no trend. Therefore, npoint2 is set high (1000) to make the trend factor negligible in the calculation:

SET HISTOGRAM = OFF
TABLE FILE VIDEOTRK
SUM TRANSTOT
BY  TRANSDATE
ON TRANSDATE RECAP SEASONAL/D10.1 = FORECAST(TRANSTOT,1,3,'SEASONAL',
   3,3,1000,1);
WHERE TRANSDATE NE '19910617'
END

In the output, npredict is 3. Therefore, three periods (nine points, nperiod * npredict) are generated.

y = mx + b

Example: Calculating a New Linear Regression Field

TABLE FILE CAR
PRINT MPG
BY DEALER_COST
WHERE MPG NE 0.0
  ON DEALER_COST RECAP FORMPG=FORECAST(MPG,1000,3,'REGRESS');
END

The output is:

DEALER_COST      MPG          FORMPG 
      2,886       27           25.51
      4,292       25           23.65
      4,631       21           23.20
      4,915       21           22.82
      5,063       23           22.63
      5,660       21           21.83
                  21           21.83
      5,800       24           21.65
      6,000       24           21.38
      7,427       16           19.49
      8,300       18           18.33
      8,400       18           18.20
     10,000       18           16.08
     11,000       18           14.75
     11,194        9           14.50
     14,940       11            9.53
     15,940        0            8.21
     16,940        0            6.88
     17,940        0            5.55

Note:

• Three predicted values of FORMPG are calculated. For values outside the range of the data, new DEALER_COST values are generated by adding the interval value (1,000) to the prior DEALER_COST value.
• There are no MPG values for the generated DEALER_COST values.
• Each FORMPG value is computed using a regression line, calculated using all of the actual data values for MPG.

  DEALER_COST is the independent variable (x) and MPG is the dependent variable (y). The equation is used to calculate MPGFORECAST trend and predicted values.

  In this case, the equation is approximately as follows:

  FORMPG = (-0.001323 * DEALER_COST) + 29.32

  The predicted values are (the values are not exactly as calculated by FORECAST because of rounding, but they show the calculation process).

Example: Generating Multiple FORECAST Columns in a Request

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.

Example: Moving the FORECAST Column

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 Sales
Coffee            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

Example: Distinguishing Data Rows From Predicted Rows

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