Create Forecast Lines on Line Chart

[#1]

Trying to figure out how to create a forecast line on a line chart to forecast disk space consumption.  Forecast line will be based on the average of the delta over last 30 days, i.e. average growth equals sum of deltas for last 30 days divided by 30 days.  Average growth then used to create forecast by accumulating it over number of days one wishes to forecast out.  Forecast line needs to be graphed as continuation of actual consumption line possibly in different color.  X-axis needs to show all dates actual and forecasted.

DATE	AVG USED	AVG DELTA
10/27/2012	295.35
10/28/2012	295.64	0.29
10/29/2012	284.42	-11.22
10/30/2012	279.14	-5.28
10/31/2012	279.54	0.40
11/1/2012	279.81	0.27
11/2/2012	280.54	0.73
11/3/2012	280.84	0.30
11/4/2012	281.18	0.34
11/5/2012	281.41	0.23
...	...	...

Any help here is appreciated.

Thanks,

Lew

[#2]

I think I may be heading down the right path, but still not there.

Attempting to calculate the average growth in a RESIDENT Load in my script, such as:

NET30_DELTA:

LOAD
  SUM(AVERAGE_DELTA) AS NET_DELTA30,
  (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH30
   RESIDENT DISC_USAGE
  WHERE DISC_DATE >=  AddMonths(MAX(DISC_DATE),-1) 

;

Once I have this AVG_GROWTH30, I should be able to use it to accumulate forcast line by adding it to AVG USED for last actual date, and then continuing to accumulate it for each subsequent day to create the trend...haven't figured out how I will combine the trend with the actual yet.   Also this RESIDENT load doesn't seem to work yet.

As I progress I will update post.  If anyone wishes to chime in with better methodology or what I might be doing wrong here, please do so.

Thanks,

Lew

[#3]

I was able to get my Forcast Lines to work so thought I'd share just in case anyone else runs into this requirement.

DISC_USAGE:

LOAD SRC_SYS_CD,
     DISC_DATE,
     VOL_TYPE,
     MIN_FREE_TB,
     MAX_FREE_TB,
     AVG_FREE_TB,
     TOTAL_TB,
     AVG_USED_TB,
     AVERAGE_DELTA
FROM
[..\..\QVD\DISC_USAGE.qvd]
(qvd);

// Calculate 30 & 90 day prior dates from last actual date
TEMP:
LOAD
  MAX(DISC_DATE) AS MaxDiscDate
  Resident DISC_USAGE;
LET vMaxDD   = peek('MaxDiscDate');
LET v30prior = Num(AddMonths(peek('MaxDiscDate'),-1));
LET v90prior = Num(AddMonths(peek('MaxDiscDate'),-3));
DROP Table TEMP;

// Calculate the avg growth rate for 30 day sample
NET30_DELTA:
LOAD
  (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH30
   RESIDENT DISC_USAGE
  WHERE DISC_DATE >= $(v30prior);
LET vAVG_GROWTH30 = peek('AVG_GROWTH30');
DROP Table NET30_DELTA;

// Calculate the avg growth rate for 90 day sample
NET90_DELTA:
LOAD
  (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH90
   RESIDENT DISC_USAGE
  WHERE DISC_DATE >= $(v90prior);
LET vAVG_GROWTH90 = peek('AVG_GROWTH90');
DROP Table NET90_DELTA;

// Calculate Trends and Load TREND Table
TREND:
LOAD
// Insure association with actual data with some common column
// names including Date -- DISC_DATE in my case.
// IN priming LOAD set Trend points to last actual date and last
// actual data point, this will force connection of trend line to
// actual line.
   'MRC' AS SRC_SYS_CD,
    DATE($(vMaxDD)) AS DISC_DATE,   
    AVG_USED_TB AS T30_AVG_USED_TB,
    AVG_USED_TB AS T90_AVG_USED_TB
    Resident DISC_USAGE
    WHERE DISC_DATE = $(vMaxDD);
FOR vCNT = 1 to 90
  LET vGrowth30 = $(vCNT) * $(vAVG_GROWTH30);
  LET vGrowth90 = $(vCNT) * $(vAVG_GROWTH90);
  Concatenate
  LOAD
    'MRC' AS SRC_SYS_CD,
     DATE($(vMaxDD) + $(vCNT))  AS DISC_DATE,
     TOTAL_TB                   AS TOTAL_TB,
//   Accumulate calculated growth 30 & 90      
     AVG_USED_TB + $(vGrowth30) AS T30_AVG_USED_TB,
     AVG_USED_TB + $(vGrowth90) AS T90_AVG_USED_TB
     Resident DISC_USAGE
     WHERE DISC_DATE = $(vMaxDD);
NEXT;

In your Chart Object, all you need to do is now include an expression for T30_AVG_USED_TB and T90_AVG_USED_TB and you've got your trend lines.

[#4 · Accepted solution]

Improved version...no Synthetic Key, no extranious data loaded, and creates trend lines for multiple boxes:

DISC_USAGE:

LOAD SRC_SYS_CD,
     DISC_DATE,
     VOL_TYPE,
     MIN_FREE_TB,
     MAX_FREE_TB,
     AVG_FREE_TB,
     TOTAL_TB,
     AVG_USED_TB,
     AVERAGE_DELTA
FROM
[..\..\QVD\DISC_USAGE.qvd]
(qvd);

BOX:

LOAD * INLINE [BOXName, VOLCnt
"BX1", 49152
"BX2", 49152
"BX3", 12288
"BX4", 12288
"BX5", 12288
"BX6", 12288
];

FOR

i=1 to $(NumRows)
    LET vBox = FieldValue('BOXName',$(i));
   // Calculate 30 & 90 day prior dates from last actual date
    NoConcatenate

   TEMP:
   LOAD
     MAX(DISC_DATE) AS MaxDiscDate
     Resident DISC_USAGE
     WHERE SRC_SYS_CD = '$(vBox)';  
   LET vMaxDD   = peek('MaxDiscDate'); 
   LET v30prior = Num(AddMonths(peek('MaxDiscDate'),-1));
   LET v90prior = Num(AddMonths(peek('MaxDiscDate'),-3));
   DROP Table TEMP;

    // Calculate the avg growth rate for 30 day sample
    NoConcatenate
    NET30_DELTA:
    LOAD
      (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH30
       RESIDENT DISC_USAGE
      WHERE DISC_DATE >= $(v30prior)
           AND SRC_SYS_CD = '$(vBox)';
    LET vAVG_GROWTH30 = peek('AVG_GROWTH30'); 
    DROP Table NET30_DELTA;

    // Calculate the avg growth rate for 90 day sample
    NoConcatenate
    NET90_DELTA:
    LOAD
      (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH90
      RESIDENT DISC_USAGE
      WHERE DISC_DATE >= $(v90prior)
           AND SRC_SYS_CD = '$(vBox)';
    LET vAVG_GROWTH90 = peek('AVG_GROWTH90');
    DROP Table NET90_DELTA;

    // Calculate Trends and concatenate trend lines to DISC_USAGE Table
    FOR vCNT = 1 to 90
      LET vGrowth30 = $(vCNT) * $(vAVG_GROWTH30);
      LET vGrowth90 = $(vCNT) * $(vAVG_GROWTH90);
      DISC_USAGE:
      Concatenate
      LOAD
        '$(vBox)' AS SRC_SYS_CD,
        DATE($(vMaxDD) + $(vCNT))  AS DISC_DATE,
        TOTAL_TB                   AS TOTAL_TB,
        $(vAVG_GROWTH30)           AS AVG_GROWTH30,
        $(vAVG_GROWTH90)           AS AVG_GROWTH90,        
        // Accumulate calculated growth 30 & 90   
        AVG_USED_TB + $(vGrowth30) AS T30_AVG_USED_TB,
        AVG_USED_TB + $(vGrowth90) AS T90_AVG_USED_TB
        Resident DISC_USAGE
        WHERE DISC_DATE = $(vMaxDD)
              AND SRC_SYS_CD = '$(vBox)';
    NEXT;
NEXT;

DROP Table BOX;

[cookiejest]

Here is a quick way using an extension that does not require data model manipulation.

https://blog.webofwork.com/predictive-line-chart-for-qlik-sense/

Uses ARIMA method dynamically on every user selection to do forward prediction.