The script is designed to utilize historical data (2018/2019) to forecast 2020. Then it automatically expands the next forecast to use three year’s data (2 historical values for each month plus one forecast value) to determine yet another forecast value. As the future inches forward and actual data expands, the forecasting process becomes increasingly more intelligent and likely more precise.
See the full tutorial and in-depth explanation about the script here.
/*
***********************************************************
Airdrop - Forecasting
Copyright (c) 2020 by Global Technologies Corporation
ALL RIGHTS RESERVED
***********************************************************
*/
// get the table name
let sourceTableName = "Sales Data";
// display app title
output.markdown('# AirDrop Forecasting');
// get the source data
let sourceTable = base.getTable(sourceTableName);
// get the data set for 2018/2019
let result = await sourceTable.selectRecordsAsync();
// set a counter for testing
var count = 0;
// build the dataframe
var aDataFrame = [];
for (var i = 1; i < 13; i++)
{
aDataFrame.push({
"Month" : i,
"2018" : 0,
"2019" : 0,
"2020" : 0,
"2021" : 0,
"2022" : 0
});
}
// iterate across all the records
for (let record of result.records)
{
if ((record.getCellValue("MONTH_ID")) && (record.getCellValue("YEAR_ID")) && (record.getCellValue("YEAR_ID") != "2005") )
{
updateDataFrame(record);
count += 1;
}
}
//
// compute the future
//
for (var i = 0; i < aDataFrame.length; i++)
{
aDataFrame[i]["2020"] = forecast(3, [aDataFrame[i]["2018"], aDataFrame[i]["2019"]], [1,2]);
aDataFrame[i]["2021"] = forecast(4, [aDataFrame[i]["2018"], aDataFrame[i]["2019"], aDataFrame[i]["2020"]], [1,2,3]);
aDataFrame[i]["2022"] = forecast(5, [aDataFrame[i]["2018"], aDataFrame[i]["2019"], aDataFrame[i]["2020"], aDataFrame[i]["2021"]], [1,2,3,4]);
}
// format the final results
for (let i in aDataFrame)
{
aDataFrame[i]["2018"] = aDataFrame[i]["2018"].toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
aDataFrame[i]["2019"] = aDataFrame[i]["2019"].toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
aDataFrame[i]["2020"] = aDataFrame[i]["2020"].toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
aDataFrame[i]["2021"] = aDataFrame[i]["2021"].toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
aDataFrame[i]["2022"] = aDataFrame[i]["2022"].toFixed(0).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
}
//
// forecast (based on triple exponential smoothing)
//
function forecast(x, ky, kx)
{
var i=0, nr=0, dr=0,ax=0,ay=0,a=0,b=0;
function average(ar) {
var r=0;
for (i=0;i < ar.length;i++){
r = r+ar[i];
}
return r/ar.length;
}
ax=average(kx);
ay=average(ky);
for (i=0;i < kx.length;i++){
nr = nr + ((kx[i]-ax) * (ky[i]-ay));
dr = dr + ((kx[i]-ax)*(kx[i]-ax))
}
b=nr/dr;
a=ay-b*ax;
return (a+b*x);
}
//
// display the forcasted dataframe
//
output.table(aDataFrame);
//
// update dataframe
//
function updateDataFrame(record)
{
try {
var yearID = parseInt(record.getCellValue("YEAR_ID")) + 15;
var itemID = findElement(aDataFrame, "Month", record.getCellValue("MONTH_ID"));
aDataFrame[itemID][yearID] += record.getCellValue("SALES");
} catch(e) {
output.inspect(e);
}
return(true);
}
//
// find an element in the aggregate list
//
function findElement(arr, propName, propValue)
{
for (var i = 0; i