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.
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.
// Calculate 30 & 90 day prior dates from last actual date TEMP: LOAD MAX(DISC_DATE) ASMaxDiscDate Resident DISC_USAGE; LETvMaxDD = peek('MaxDiscDate'); LETv30prior = Num(AddMonths(peek('MaxDiscDate'),-1)); LETv90prior = Num(AddMonths(peek('MaxDiscDate'),-3)); DROPTable TEMP;
// Calculate the avg growth rate for 30 day sample NET30_DELTA: LOAD (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) ASAVG_GROWTH30 RESIDENT DISC_USAGE WHEREDISC_DATE >= $(v30prior); LETvAVG_GROWTH30 = peek('AVG_GROWTH30'); DROPTable NET30_DELTA;
// Calculate the avg growth rate for 90 day sample NET90_DELTA: LOAD (SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) ASAVG_GROWTH90 RESIDENT DISC_USAGE WHEREDISC_DATE >= $(v90prior); LETvAVG_GROWTH90 = peek('AVG_GROWTH90'); DROPTable 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' ASSRC_SYS_CD, DATE($(vMaxDD)) ASDISC_DATE, AVG_USED_TBAST30_AVG_USED_TB, AVG_USED_TBAST90_AVG_USED_TB Resident DISC_USAGE WHEREDISC_DATE = $(vMaxDD); FORvCNT = 1 to 90 LETvGrowth30 = $(vCNT) * $(vAVG_GROWTH30); LETvGrowth90 = $(vCNT) * $(vAVG_GROWTH90); Concatenate LOAD 'MRC' ASSRC_SYS_CD, DATE($(vMaxDD) + $(vCNT)) ASDISC_DATE, TOTAL_TBASTOTAL_TB, // Accumulate calculated growth 30 & 90 AVG_USED_TB + $(vGrowth30)AST30_AVG_USED_TB, AVG_USED_TB + $(vGrowth90)AST90_AVG_USED_TB Resident DISC_USAGE WHEREDISC_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.