Hi,
This report shows the income expense from beginning of last year, up to beginning of this month. It also plots the income/expense on a graph with the months labelled. This is the one i was having trouble with getting the label data through lua, but i've ended up not using it at all. The JavaScript just access the columns directly.
Shows Income Expense from last year with graph
Moderator: Renato
Shows Income Expense from last year with graph
- Attachments
-
- Income Expense Year.grm
- (1.88 KiB) Downloaded 849 times
Re: Shows Income Expense from last year with graph
Hi,
cool report.
If I may give you a little SQL hint to shorten the query: You can avoid the self join by using conditional sums with the group-by:
As you can see, the query just sums the transamount as income, if the transcode equals 'Deposit', and as outcome, if the transcode equals 'Withdrawal'. So, no self-join is necessary any more.
The two relevant values for TransCode are selected by the where clause. This is useful, as the where clause will be evaluated before the grouping.
Perhaps you ccould need that for further reports.
cool report.
If I may give you a little SQL hint to shorten the query: You can avoid the self join by using conditional sums with the group-by:
Code: Select all
select transdate,dep.date, Income,Outcome,Income-Outcome as Profit
from
(
select
strftime('%Y', CHECKINGACCOUNT_V1.TRANSDATE) as YEAR,
strftime('%m', CHECKINGACCOUNT_V1.TRANSDATE) as MONTH,
strftime('%m-%Y', CHECKINGACCOUNT_V1.TRANSDATE) as DATE,
CHECKINGACCOUNT_V1.TRANSDATE,
sum(case when Transcode = 'Deposit' then TRANSAMOUNT else 0 end) as Income,
sum(case when Transcode = 'Withdrawal' then TRANSAMOUNT else 0 end) as Outcome
from
CHECKINGACCOUNT_V1
join
ACCOUNTLIST_V1 on CHECKINGACCOUNT_V1.ACCOUNTID=ACCOUNTLIST_V1.ACCOUNTID
join
PAYEE_V1 on PAYEE_V1.PAYEEID=CHECKINGACCOUNT_V1.PAYEEID
where TransCode in ('Deposit', 'Withdrawal')
group by year ,month
) as dep
WHERE (dep.transdate >= date('now', 'start of YEAR', '-1 year') and dep.transdate < date('now', 'start of month'))
order by year,month;
The two relevant values for TransCode are selected by the where clause. This is useful, as the where clause will be evaluated before the grouping.
Perhaps you ccould need that for further reports.
Kind regards,
nuvito
nuvito
Re: Shows Income Expense from last year with graph
Thank you so much for your feedback. All is welcome. I'm a programmer by trade but not web or SQL, so this is a bit of a hobby.
I've got another report to post in a bit and id be very interested in feedback on that because I feel it could be optimised even more than this one.
I've got another report to post in a bit and id be very interested in feedback on that because I feel it could be optimised even more than this one.
-
- Developer
- Posts: 382
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Shows Income Expense from last year with graph
Maybe we can post it in Github?
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)