Disclaimer: Forecast accuracy depends on correlating the method used to the data. These are only intended to be an example of some different methods and how they can be implemented.
Asset Forecast Report (uses the appreciation/depreciation rates entered for each asset)
[attachment=1]Asset Forecast Report.grm[/attachment]
SQL
[code]
SELECT STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE
FROM ASSETS_V1;
[/code]
Lua
[code]
local total = 0;
local forecast = {0, 0, 0, 0, 0}
local period = 5;
function is_leap_year(year)
local ly = 0;
if year % 4 == 0 then
if year % 100 == 0 then
if year % 400 == 0 then
ly = 1;
end
else
ly = 1;
end
end
return ly;
end
function get_days(month, year)
local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }
local d = days[tonumber(month)];
if tonumber(month) == 2 and is_leap_year(year) ~= 0 then
d = 29;
end
return d;
end
function get_years(record)
local date = record:get("STARTDATE");
local aYear = string.sub(date,1,4);
local aMonth = string.sub(date,6, 7);
local aDay = string.sub(date,9);
local cYear = os.date('%Y');
local cMonth = os.date('%m');
local cDay = os.date('%d');
local nbrYears = 0;
if aYear == cYear then
local days = 0;
if aMonth == cMonth then
days = cDay - aDay;
else
days = get_days(aMonth, aYear) - aDay;
if tonumber(aMonth) + 1 > tonumber(cMonth) - 1 then
for i = (aMonth + 1), (cMonth - 1) do
days = days + get_days(i, aYear);
end
end
days = days + cDay;
end
if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
nbrYears = days / 366;
else
nbrYears = days / 365;
end
else
nbrYears = cYear - aYear;
local days = get_days(aMonth, aYear) - aDay;
for i = (aMonth + 1), 12 do
days = days + get_days(i, aYear);
end
if tonumber(cMonth) > 1 then
for i = 1, (cMonth - 1) do
days = days + get_days(i, cYear);
end
end
days = days + cDay;
if tonumber(aMonth) < tonumber(cMonth) then
nbrYears = nbrYears - 1;
elseif aMonth == cMonth and tonumber(aDay) <= tonumber(cDay) then
nbrYears = nbrYears - 1;
end
if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
nbrYears = nbrYears + (days / 366);
else
nbrYears = nbrYears + (days / 365);
end
end
return nbrYears;
end
function handle_record(record)
local value = record:get("VALUE");
local fvalue = {}
for i = 1,period do
fvalue[i] = value;
end
local change = record:get("VALUECHANGE");
if change ~= 'None' then
local rate = record:get("VALUECHANGERATE") / 100;
local years = get_years(record);
local scale = 1;
local fscale = {}
for i = 1,period do
fscale[i] = scale;
end
if change == 'Appreciates' then
scale = math.pow(1 + rate, years);
for i = 1,period do
fscale[i] = math.pow(1 + rate, years + i);
end
elseif change == 'Depreciates' then
scale = math.pow(1 - rate, years);
for i = 1,period do
fscale[i] = math.pow(1 - rate, years + i);
end
end
value = value * scale;
for i = 1,period do
fvalue[i] = fvalue[i] * fscale[i];
end
end
total = total + value;
for i = 1,period do
forecast[i] = forecast[i] + fvalue[i];
end
end
function complete(result)
local curYear = os.date('%Y');
local curMonth = os.date('%m');
local curDay = os.date('%d');
result:set("Current_Total", total);
for i = 1,period do
result:set("Year" .. i .. "_Total", forecast[i]);
local date = os.date("*t", os.time{year=curYear+i,month=curMonth,day=curDay});
result:set("Year" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day));
end
end
[/code]
Template
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title><TMPL_VAR REPORTNAME></title>
<link href = "master.css" rel = "stylesheet">
</head>
<body>
<div class="container">
<h3>Asset Forecast Report</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-2"></div>
<div class="col-xs-8">
<table class="table">
<thead>
<tr>
<th>Date</th>
<th class="text-right">Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>Current</td>
<td class="money text-right"><TMPL_VAR Current_Total></td>
</tr>
</tbody>
<tfoot>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>1 year <TMPL_VAR Year1_Label></td>
<td class="money text-right"><TMPL_VAR Year1_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>2 years <TMPL_VAR Year2_Label></td>
<td class="money text-right"><TMPL_VAR Year2_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>3 years <TMPL_VAR Year3_Label></td>
<td class="money text-right"><TMPL_VAR Year3_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>4 years <TMPL_VAR Year4_Label></td>
<td class="money text-right"><TMPL_VAR Year4_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>5 years <TMPL_VAR Year5_Label></td>
<td class="money text-right"><TMPL_VAR Year5_Total></td>
</tr>
</tfoot>
</table>
</div>
<table class="table">
<tbody>
<TMPL_LOOP ERRORS>
<tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
<tr><td><TMPL_VAR ERROR></td></tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
</div>
</body>
<script type="text/javascript">
<!-- Format numbers -->
function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
</script>
</html>
[/code]
Account Forecast Report (uses data from repeating transactions)
[attachment=3]Account Forecast Report.grm[/attachment]
SQL ([color=#FF0000]Update account name in line 24.[/color])
[code]
select TRANSAMOUNT, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE,
(
select INITIALBAL + total(TRANSAMOUNT)
from
(select ACCOUNTID, STATUS,
(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE = 'Transfer') as t
where t.ACCOUNTID = a.ACCOUNTID
and t.STATUS <> 'V') as Balance
from
(select ACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE,
(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT
from BILLSDEPOSITS_V1
union all
select TOACCOUNTID, STATUS, REPEATS, NUMOCCURRENCES, NEXTOCCURRENCEDATE, TOTRANSAMOUNT
from BILLSDEPOSITS_V1
where TRANSCODE = 'Transfer') as b
inner join ACCOUNTLIST_V1 as a
on b.ACCOUNTID = a.ACCOUNTID
where ACCOUNTNAME = 'Account1'
and b.STATUS <> 'V';
[/code]
Lua
[code]
local total = 0;
local forecast = {}
local period = 6;
local initialized = 0;
local repeatcode = 0;
local numrepeats = 0;
function is_leap_year(year)
local ly = 0;
if year % 4 == 0 then
if year % 100 == 0 then
if year % 400 == 0 then
ly = 1;
end
else
ly = 1;
end
end
return ly;
end
function get_days(month, year)
local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }
local d = days[tonumber(month)];
if tonumber(month) == 2 and is_leap_year(year) ~= 0 then
d = 29;
end
return d;
end
function get_time_difference(year, month, day)
local curYear = os.date('%Y');
local curMonth = os.date('%m');
local curDay = os.date('%d');
local d = day - curDay;
local m = 0;
if d < 0 then
d = d + get_days(curMonth, curYear);
m = -1;
end
m = m + month - curMonth;
local y = 0;
if m < 0 then
m = m + 12;
y = -1;
end
y = y + year - curYear;
return {y, m, d}
end
function get_next_date(record, previousYear, previousMonth, previousDay)
local originalnumber = tonumber(record:get("NUMOCCURRENCES"));
-- Auto Execute User Acknowlegement required
if repeatcode >= 100 then
repeatcode = repeatcode - 100;
end
-- Auto Execute Silent mode
if repeatcode >= 100 then
repeatcode = repeatcode - 100;
end
if repeatcode ~= -1 then
if repeatcode < 11 or repeatcode > 14 then
numrepeats = numrepeats - 1;
end
end
local nextDate= os.time{year=previousYear,month=previousMonth,day=previousDay}
local updatedate = 0;
if originalnumber == -1 then
updatedate = 1;
elseif numrepeats > 0 then
updatedate = 1;
end
if updatedate ~= 0 then
-- weekly
if repeatcode == 1 then
nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 7}
-- biweekly
elseif repeatcode == 2 then
nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 14}
-- month
elseif repeatcode == 3 then
nextDate = os.time{year=previousYear,month=previousMonth + 1,day=previousDay}
-- bimonth
elseif repeatcode == 4 then
nextDate = os.time{year=previousYear,month=previousMonth + 2,day=previousDay}
-- quarterly
elseif repeatcode == 5 then
nextDate = os.time{year=previousYear,month=previousMonth + 3,day=previousDay}
-- half yearly
elseif repeatcode == 6 then
nextDate = os.time{year=previousYear,month=previousMonth + 6,day=previousDay}
-- yearly
elseif repeatcode == 7 then
nextDate = os.time{year=previousYear + 1,month=previousMonth,day=previousDay}
-- quad monthly
elseif repeatcode == 8 then
nextDate = os.time{year=previousYear,month=previousMonth + 4,day=previousDay}
-- quad weekly
elseif repeatcode == 9 then
nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 28}
-- daily
elseif repeatcode == 10 then
nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + 1}
-- repeat in X days or repeat in X months
elseif repeatcode == 11 or repeatcode == 12 then
if numrepeats ~= -1 then
numrepeats = -1;
end
-- every X days
elseif repeatcode == 13 then
nextDate = os.time{year=previousYear,month=previousMonth,day=previousDay + numrepeats}
-- every X months
elseif repeatcode == 14 then
nextDate = os.time{year=previousYear,month=previousMonth + numrepeats,day=previousDay}
-- month last day or monthly last business day
elseif repeatcode == 15 or repeatcode == 16 then
local m = previousMonth + 1;
local y = previousYear;
if m > 12 then
m = 1;
y = y + 1;
end
nextDate = os.time{year=y,month=m,day=get_days(m, y)}
-- monthly last business day
if repeatcode == 16 then
local n = os.date("*t", nextDate);
-- Sunday
if n.wday == 0 then
nextDate = os.time{year=n.year,month=n.month,day=n.day - 2}
-- Saturday
elseif n.wday == 7 then
nextDate = os.time{year=n.year,month=n.month,day=n.day - 1}
end
end
end
end
local next = os.date("*t", nextDate);
return {next.year, next.month, next.day}
end
function handle_record(record)
if initialized == 0 then
total = record:get("Balance");
for i=1,period do
forecast[i] = total;
end
initialized = 1;
end
repeatcode = tonumber(record:get("REPEATS"));
numrepeats = record:get("NUMOCCURRENCES");
local amount = record:get("TRANSAMOUNT");
local nextDate = record:get("NEXTOCCURRENCEDATE");
local nextYear = tonumber(string.sub(nextDate,1,4));
local nextMonth = tonumber(string.sub(nextDate,6, 7));
local nextDay = tonumber(string.sub(nextDate,9));
local timediff = get_time_difference(nextYear, nextMonth, nextDay);
while (timediff[2] < period and timediff[1] == 0) or timediff[1] < 0 do
local m = timediff[2] + 1;
if timediff[1] < 0 then
m = 1;
end
for i=m,period do
forecast[i] = forecast[i] + amount;
end
local next = get_next_date(record, nextYear, nextMonth, nextDay);
if next[1] == nextYear and next[2] == nextMonth and next[3] == nextDay then
break;
end
nextYear = next[1];
nextMonth = next[2];
nextDay = next[3];
timediff = get_time_difference(nextYear, nextMonth, nextDay);
end
end
function complete(result)
local curYear = os.date('%Y');
local curMonth = os.date('%m');
local curDay = os.date('%d');
result:set("Current_Total", total);
for i=1,period do
result:set("Month" .. i .. "_Total", forecast[i]);
local date = os.date("*t", os.time{year=curYear,month=curMonth+i,day=curDay});
result:set("Month" .. i .. "_Label", string.format("(%d-%02d-%02d)",date.year,date.month,date.day));
end
end
[/code]
Template
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title><TMPL_VAR REPORTNAME></title>
<link href = "master.css" rel = "stylesheet">
</head>
<body>
<div class="container">
<h3>Account Forecast Report</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-2"></div>
<div class="col-xs-8">
<table class="table">
<thead>
<tr>
<th>Date</th>
<th class="text-right">Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>Current</td>
<td class="money text-right"><TMPL_VAR Current_Total></td>
</tr>
</tbody>
<tfoot>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>1 month <TMPL_VAR Month1_Label></td>
<td class="money text-right"><TMPL_VAR Month1_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>2 months <TMPL_VAR Month2_Label></td>
<td class="money text-right"><TMPL_VAR Month2_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>3 months <TMPL_VAR Month3_Label></td>
<td class="money text-right"><TMPL_VAR Month3_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>4 months <TMPL_VAR Month4_Label></td>
<td class="money text-right"><TMPL_VAR Month4_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>5 months <TMPL_VAR Month5_Label></td>
<td class="money text-right"><TMPL_VAR Month5_Total></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td>6 months <TMPL_VAR Month6_Label></td>
<td class="money text-right"><TMPL_VAR Month6_Total></td>
</tr>
</tfoot>
</table>
</div>
<table class="table">
<tbody>
<TMPL_LOOP ERRORS>
<tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
<tr><td><TMPL_VAR ERROR></td></tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
</div>
</body>
<script type="text/javascript">
<!-- Format numbers -->
function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
</script>
</html>
[/code]
Account Trend with Forecast Report (uses linear regression)
[attachment=2]Account Trend with Forecast Report.grm[/attachment]
SQL ([color=#FF0000]Update account name in line 29.[/color])
[code]
select strftime('%Y', TRANSDATE) as YEAR
, strftime('%m', TRANSDATE) as MONTH
, '' as DATE
, total(TRANSAMOUNT)
+ (select INITIALBAL + total(TRANSAMOUNT)
from
(select ACCOUNTID, TRANSDATE, STATUS,
(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE = 'Transfer') t2
where t2.ACCOUNTID = t1.ACCOUNTID
and t2.STATUS <> 'V'
and (strftime('%Y', t2.TRANSDATE) < strftime('%Y', t1.TRANSDATE)
or (strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
and strftime('%m', t2.TRANSDATE) < strftime('%m', t1.TRANSDATE)))
) as Balance
from
(select ACCOUNTID, TRANSDATE, STATUS,
(case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as transamount
from CHECKINGACCOUNT_V1
union all
select TOACCOUNTID, TRANSDATE, STATUS, TOTRANSAMOUNT
from CHECKINGACCOUNT_V1
where TRANSCODE = 'Transfer') t1
inner join ACCOUNTLIST_V1 a on a.ACCOUNTID = t1.ACCOUNTID
where ACCOUNTNAME = 'Account1'
and t1.STATUS <> 'V'
group by YEAR, MONTH
order by YEAR asc, MONTH asc;
[/code]
Lua
[code]
local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local labels = 'labels : [';
local data = 'datasets : [{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(0,0,0,0.5)",pointColor:"rgba(0,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:[';
-- forecast date
local year = 0;
local month = 0;
-- Forecast line variables (Linear Regression)
local count = 0;
local count_sum = 0;
local balance_sum = 0;
local product_sum = 0;
local squared_sum = 0;
function handle_record(record)
year = record:get("YEAR");
month = record:get("MONTH");
local date = months[tonumber(month)] .. ' ' .. year;
labels = labels .. '"' .. date .. '",';
record:set("DATE", date);
local balance = string.format("%.2f", tonumber(record:get("Balance")));
if tonumber(string.sub(balance,-1)) == 0 and tonumber(string.sub(balance,-2)) ~= 0 then
data = data .. '\'' .. balance .. '\',';
else
data = data .. balance .. ',';
end
-- Update forecast line calculation values
local x = count + 1;
count = count + 1;
count_sum = count_sum + x;
balance_sum = balance_sum + balance;
product_sum = product_sum + (x * balance);
squared_sum = squared_sum + (x * x);
end
function complete(result)
-- Forecast line equation
local slope = ((count * product_sum) - (count_sum * balance_sum)) / ((count * squared_sum) - (count_sum * count_sum));
local intercept = (balance_sum - (slope * count_sum)) / count;
-- Forecast line values
data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(255,0,0,0.5)",pointColor:"rgba(255,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:[';
for i=1,count do
data = data .. ',';
end
for i=1,12 do
-- Label
month = month + 1;
if month > 12 then
year = year + 1;
month = 1;
end
local date = months[tonumber(month)] .. ' ' .. year;
labels = labels .. '"' .. date .. '",';
result:set('FLABEL' .. i, date);
-- Value
local value = (slope * (count + i)) + intercept;
local fval = string.format("%.2f", value);
if tonumber(string.sub(fval,-1)) == 0 and tonumber(string.sub(fval,-2)) ~= 0 then
data = data .. '\'' .. fval .. '\',';
else
data = data .. fval .. ',';
end
result:set('FVALUE' .. i, string.format("%.2f", value));
end
result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
end
[/code]
Template
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title><TMPL_VAR REPORTNAME></title>
<link href = "master.css" rel = "stylesheet">
<script src="Chart.js"></script>
</head>
<body>
<div class="container">
<h3>Account Trend Report</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-2"></div>
<div class="col-xs-8">
<table class "table-condensed">
<tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr>
<tr><td> </td></tr>
<tr><td><table class="table">
<thead>
<tr>
<th>Date</th>
<th class="text-right">Balance</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR DATE></td>
<td class="money text-right"><TMPL_VAR Balance></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL1></td>
<td class="money text-right"><TMPL_VAR FVALUE1></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL2></td>
<td class="money text-right"><TMPL_VAR FVALUE2></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL3></td>
<td class="money text-right"><TMPL_VAR FVALUE3></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL4></td>
<td class="money text-right"><TMPL_VAR FVALUE4></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL5></td>
<td class="money text-right"><TMPL_VAR FVALUE5></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL6></td>
<td class="money text-right"><TMPL_VAR FVALUE6></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL7></td>
<td class="money text-right"><TMPL_VAR FVALUE7></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL8></td>
<td class="money text-right"><TMPL_VAR FVALUE8></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL9></td>
<td class="money text-right"><TMPL_VAR FVALUE9></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL10></td>
<td class="money text-right"><TMPL_VAR FVALUE10></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL11></td>
<td class="money text-right"><TMPL_VAR FVALUE11></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL12></td>
<td class="money text-right"><TMPL_VAR FVALUE12></td>
</tr>
</tfoot>
</table></td></tr></table>
</div>
<table class="table">
<tbody>
<TMPL_LOOP ERRORS>
<tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
<tr><td><TMPL_VAR ERROR></td></tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
</div>
</body>
<script type="text/javascript">
<!-- Format numbers -->
function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
<!-- Chart -->
var data = {<TMPL_VAR "TREND_DATA">}
var ctx = document.getElementById("reportChart").getContext("2d");
var reportChart = new Chart(ctx).Line(data);
</script>
</html>
[/code]
Category Trend with Forecast Report (uses historical average)
[attachment=0]Category Trend with Forecast Report.grm[/attachment]
SQL ([color=#FF0000]Update category and subcategory names in line 21.[/color])
[code]
select strftime('%Y', t1.TRANSDATE) as YEAR
, strftime('%m', t1.TRANSDATE) as MONTH
, '' as DATE
, total(t1.TRANSAMOUNT * c1.BASECONVRATE)
+ (select total(split.SPLITTRANSAMOUNT * c2.BASECONVRATE) from CHECKINGACCOUNT_V1 as t2
inner join ACCOUNTLIST_V1 as a2 on a2.ACCOUNTID = t1.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c2 on c2.CURRENCYID = a2.CURRENCYID
inner join SPLITTRANSACTIONS_V1 as split on t2.TRANSID = split.TRANSID
where strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
and strftime('%m', t2.TRANSDATE) = strftime('%m', t1.TRANSDATE)
and split.CATEGID = cat.CATEGID
and split.SUBCATEGID = subcat.SUBCATEGID
and t2.STATUS <> 'V') as AMOUNT
from CHECKINGACCOUNT_V1 as t1
inner join ACCOUNTLIST_V1 as a1 on a1.ACCOUNTID = t1.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID
inner join CATEGORY_V1 as cat
on t1.CATEGID = cat.CATEGID
inner join SUBCATEGORY_V1 as subcat
on t1.SUBCATEGID = subcat.SUBCATEGID
where cat.CATEGNAME = 'Food' and subcat.SUBCATEGNAME = 'Groceries'
and t1.STATUS <> 'V'
group by year, month
order by year asc, month asc;
[/code]
Lua
[code]
local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local labels = 'labels : [';
local data = 'datasets : [{fillColor : "rgba(0,0,0,0.5)", strokeColor : "rgba(0,0,0,0.5)", data : [';
local total = 0;
-- forecast date
local year = 0;
local month = 0;
-- Forecast line variables (Average)
local average = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
local count = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
function handle_record(record)
year = record:get("YEAR");
month = record:get("MONTH");
local date = months[tonumber(month)] .. ' ' .. year;
labels = labels .. '"' .. date .. '",';
record:set("DATE", date);
local amount = string.format("%.2f", tonumber(record:get("AMOUNT")));
if tonumber(string.sub(amount,-1)) == 0 and tonumber(string.sub(amount,-2)) ~= 0 then
data = data .. '\'' .. amount .. '\',';
else
data = data .. amount .. ',';
end
total = total + 1;
-- Update forecast line calculation values
count[tonumber(month)] = count[tonumber(month)] + 1;
average[tonumber(month)] = average[tonumber(month)] + amount;
end
function complete(result)
-- Forecast line values
data = string.sub(data,1,-2) .. ']},{fillColor:"rgba(255,0,0,0.5)",strokeColor:"rgba(255,0,0,0.5)",data:[';
for i=1,total do
data = data .. ',';
end
for i=1,12 do
-- Label
month = month + 1;
if month > 12 then
year = year + 1;
month = 1;
end
local date = months[tonumber(month)] .. ' ' .. year;
labels = labels .. '"' .. date .. '",';
result:set('FLABEL' .. i, date);
-- Value
local value = "0";
if (count[tonumber(month)] > 0) then
value = string.format("%.2f", average[tonumber(month)] / count[tonumber(month)]);
end
local fval = string.format("%.2f", value);
if tonumber(string.sub(fval,-1)) == 0 and tonumber(string.sub(fval,-2)) ~= 0 then
data = data .. '\'' .. fval .. '\',';
else
data = data .. fval .. ',';
end
result:set('FVALUE' .. i, string.format("%.2f", value));
end
result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
end
[/code]
Template
[code]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title><TMPL_VAR REPORTNAME></title>
<STYLE><TMPL_INCLUDE NAME="master.css"></STYLE>
<SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT>
</head>
<body>
<div class="container">
<h3>Category Trend Report</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-2"></div>
<div class="col-xs-8">
<table class "table-condensed">
<tr><td><canvas id="reportChart" width="600" height="300"></canvas></td></tr>
<tr><td> </td></tr>
<tr><td><table class="table">
<thead>
<tr>
<th>Date</th>
<th class="text-right">Amount</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR DATE></td>
<td class="money text-right"><TMPL_VAR AMOUNT></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL1></td>
<td class="money text-right"><TMPL_VAR FVALUE1></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL2></td>
<td class="money text-right"><TMPL_VAR FVALUE2></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL3></td>
<td class="money text-right"><TMPL_VAR FVALUE3></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL4></td>
<td class="money text-right"><TMPL_VAR FVALUE4></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL5></td>
<td class="money text-right"><TMPL_VAR FVALUE5></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL6></td>
<td class="money text-right"><TMPL_VAR FVALUE6></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL7></td>
<td class="money text-right"><TMPL_VAR FVALUE7></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL8></td>
<td class="money text-right"><TMPL_VAR FVALUE8></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL9></td>
<td class="money text-right"><TMPL_VAR FVALUE9></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL10></td>
<td class="money text-right"><TMPL_VAR FVALUE10></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL11></td>
<td class="money text-right"><TMPL_VAR FVALUE11></td>
</tr>
<tr bgcolor='rgba(255,0,0,0.5)'>
<td><TMPL_VAR FLABEL12></td>
<td class="money text-right"><TMPL_VAR FVALUE12></td>
</tr>
</tfoot>
</table></td></tr></table>
</div>
<table class="table">
<tbody>
<TMPL_LOOP ERRORS>
<tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
<tr><td><TMPL_VAR ERROR></td></tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
</div>
</body>
<script type="text/javascript">
<!-- Format numbers -->
function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
<!-- Chart -->
var data = {<TMPL_VAR "TREND_DATA">}
var ctx = document.getElementById("reportChart").getContext("2d");
var reportChart = new Chart(ctx).Bar(data);
</script>
</html>
[/code]
You do not have the required permissions to view the files attached to this post.