Shows Income Expense from last year with graph

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Shows Income Expense from last year with graph

Post by sterling »

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.
Attachments
Income Expense Year.grm
(1.88 KiB) Downloaded 563 times

nuvito
New MMEX User
Posts: 10
Joined: Mon Jun 11, 2012 5:15 pm
Are you a spam bot?: No

Re: Shows Income Expense from last year with graph

Post by nuvito »

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:

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;
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.
Kind regards,

nuvito

sterling
Super MMEX User
Posts: 72
Joined: Thu Dec 11, 2014 8:40 am
Are you a spam bot?: No

Re: Shows Income Expense from last year with graph

Post by sterling »

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.

guanlisheng
MMEX Developer
Posts: 205
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

Post by guanlisheng »

Maybe we can post it in Github?

Post Reply