Insights How To Add Accumulative Custom Line To a Widget
Script for Accumulative Monthly Targets
Custom widget scripts can only be written by Professional Services and not supported by Maximizer Customer Service.
The hardcoded line is only visible on the live dashboards and on widget exports as PNG. The line is not visible on the reports; such as scheduled email delivery or exporting to PDF.
Custom scripts increase dashboard loading time.
This article explains how to create a hardcoded data set for Accumulated Target by Month; represented by the red 'Target' line below. This solution works for values that don't change often; as any modifications must be done in the script.
Note; it doesn't work for rolling dates or other categories that involve changing X-axis labels.
This new static line doesn't respond to any filters.
Step by step How-to
Create a widget without a target line. In this example we have a column chart with two Values or data sets:
Acc Totals This Year: YTDSUM([Revenue])
Acc Totals Last Year: PASTYEAR(YTDSUM([Revenue]))
Format X-axis. You will be referencing X-axis display values in the custom script. In this example: 'Jan'. 'Feb' etc.
Any changes to X-axis display values; later on; must be reflected in the widget script.
Now let's add a custom target line. Go to the 'Edit script' on the top right.
Copy the Script below and make necessary changes (in pink).
/* Uncomment this line if an annual budget is set (same amount per month) */ // let annualBudget = 1200000; /* Uncomment this line if a monthly budget is set (different amounts per month) */ //let monthlyBudgets = [100000; 150000; 200000; 100000; 150000; 200000; 100000; 150000; 200000; 100000; 150000; 200000]; /* Set to true for cumulative YDT values; false for monthly values */
let IsYTD = true; let lineColor = "red"; let lineName = "Target";
/* Do Not Edit below this Line */ /*------------------------------*/
if (!(typeof monthlyBudgets === 'undefined')) { for (let i = 0; i < 12; i++) { monthlyBudgetYTD[i] = monthlyBudgets[i] + (IsYTD ? (i>0 ? monthlyBudgetYTD[i-1] : 0) : 0); dataItems[months[i]] = monthlyBudgetYTD[i]; } } else { let monthlyBudget = annualBudget/ 12; for (let i = 0; i < 12; i++) { monthlyBudgetYTD[i] = monthlyBudget + (IsYTD ? (i>0 ? monthlyBudgetYTD[i-1] : 0) : 0); dataItems[months[i]] = monthlyBudgetYTD[i]; } }
//console.log(JSON.stringify(dataItems));
console.log(args.result.xAxis.categories);
let series = args.result.series; if (series != null && series.length > 0) { let duplicatedSeries = $.extend(true; {}; series[0]);
duplicatedSeries.type = "line"; duplicatedSeries.name = lineName; duplicatedSeries.color = lineColor; duplicatedSeries.isMaxCustom = true; let datas = duplicatedSeries.data; let categories = args.result.xAxis.categories; for (let i = 0; i < datas.length; i++) { let cd = categories[i]; if (dataItems[cd] != null) { datas[i].y = dataItems[cd]; } } series.push(duplicatedSeries); } });
You can either set the annual budget that would be evenly distributed between the months or set monthly budgets. Click 'SAVE' when you are done.
Now go back to the widget designer; click 'Apply' changes.
Important: refresh your browser before giving it a try.
The hardcoded line will show only for the X-axis categories with values. If you have sparse data we recommend you add the script below as well
Additional script for sparse data
In some cases; we have only a few data points but prefer to see all values on X-axis. For example; we are in the first month of our fiscal year; and we would like to build a chart that shows us all months till the end of the year; even if we currently don't have any data for upcoming months.
By adding this part on the top of the previous script; you will see all months on the X-axis.
let series = args.rawResult.values; if (series != null && series.length > 0) { let monthDatas = []; for (let i = 0; i < series.length; i++) { let d = series[i][0].data; let m = (new Date(d)).getMonth(); monthDatas[m] = series[i]; }
// add data into series for missing months for (let i = 0; i <12; i++) { if (monthDatas[i] == null) { let cloneData = $.extend(true; {}; series[0]); let date = new Date(cloneData[0].data); date.setMonth(i); cloneData[0].data = date; cloneData[1].data = 0; series.splice(i; 0; cloneData); } }
// Reorder series according to fiscal year let months = ["jan"; "feb"; "mar"; "apr"; "may"; "jun"; "jul"; "aug"; "sep"; "oct"; "nov"; "dec"]; let fy = 0; //get fiscial year if (args.query.metadata != null && args.query.metadata.length > 0) { let sFY = args.query.metadata[0].jaql != null ? args.query.metadata[0].jaql.fiscal : ""; if (sFY != null && sFY.length > 0) { let fy = months.indexOf(sFY); if (fy > 0) { //reorder series for (let i = 0; i < fy; i++) { series.push(series.splice(0; 1)[0]); } } } }
// accumulate data if (accumulate) { for (let i = 1; i <12; i++) { if (series[i][1].data == 0.0) { series[i][1].data = series[i - 1][1].data; } } } }
});
Carefully check the syntax so visually the script looks like this in the script editor:
