3 Replies Latest reply: Feb 21, 2013 12:06 PM by LEWIS GOLDSTEIN RSS

    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

        • Re: Create Forecast Lines on Line Chart

          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

            • Re: Create Forecast Lines on Line Chart

              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.

                • Re: Create Forecast Lines on Line Chart

                  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;