Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Create Forecast Lines on Line Chart

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.

DATEAVG USEDAVG DELTA
10/27/2012295.35
10/28/2012295.640.29
10/29/2012284.42-11.22
10/30/2012279.14-5.28
10/31/2012279.540.40
11/1/2012279.810.27
11/2/2012280.540.73
11/3/2012280.840.30
11/4/2012281.180.34
11/5/2012281.41

0.23

.........

Any help here is appreciated.

Thanks,

Lew

1 Solution

Accepted Solutions
Highlighted
Not applicable

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;

View solution in original post

4 Replies
Highlighted
Not applicable

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

Highlighted
Not applicable

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.

Highlighted
Not applicable

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;

View solution in original post

Highlighted
Partner
Partner

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.