Income vs Expences

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Income vs Expences

Post by Nikolay »

Code: Select all

select 
strftime('%m', TRANSDATE) as Month,
total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (select c.TRANSDATE,
c.TRANSCODE,
case when c.categid=-1 then s.splittransamount else c.transamount end as amount

from checkingaccount_v1 c 
left join splittransactions_v1 s on s.transid = c.transid
where c.TRANSCODE <>'Transfer'
--and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
and TRANSDATE >= date('now','start of month','-2 month') and  TRANSDATE <= date('now','start of month','+1 month','-1 day') ) t
group by strftime('%m', TRANSDATE)
MarcusAurelios
New MMEX User
Posts: 3
Joined: Mon Mar 24, 2014 3:12 pm
Are you a spam bot?: No

Re: Income vs Expences

Post by MarcusAurelios »

Hi Nikolay,

On the income v expenses report it excludes transfers, however I have used MMEX to show my credit cards as well as my bank accounts and the transfers represent payments to the credit cards, is it possible to have the option of adding these?

Regards
thompsonson
New MMEX User
Posts: 8
Joined: Sat Sep 01, 2012 9:32 am
Are you a spam bot?: No

Re: Income vs Expences

Post by thompsonson »

I do similar to you with our credit cards. What I've found is this SQL works in this case. The transactions on the credit card are part of the expense column (refunds are in the income column). You can see it broken down with this SQL.

Code: Select all

select
    strftime('%m', TRANSDATE) as Month,
    t.ACCOUNTID,
    a.ACCOUNTNAME,
    total (case when TRANSCODE='Deposit' then amount else 0 end) as Deposit,
    total (case when TRANSCODE='Withdrawal' then amount else 0 end) as Withdrawal
from (
    select 
        c.TRANSDATE,
        c.TRANSCODE,
        c.ACCOUNTID,
        case when c.categid=-1 then s.splittransamount else c.transamount end as amount
    from 
        checkingaccount_v1 c
        left join splittransactions_v1 s on s.transid = c.transid
    where 
        c.TRANSCODE <>'Transfer'
        --and strftime('%m', TRANSDATE)='01' and strftime('%Y', TRANSDATE)='2014'
        and TRANSDATE >= date('now','start of month','-2 month') and  TRANSDATE <= date('now','start of month','+1 month','-1 day') 
) t
left join accountlist_v1 a on a.accountid = t.accountid
group by 
    strftime('%m', TRANSDATE), 
    t.ACCOUNTID
;
MarcusAurelios
New MMEX User
Posts: 3
Joined: Mon Mar 24, 2014 3:12 pm
Are you a spam bot?: No

Re: Income vs Expences

Post by MarcusAurelios »

Thanks for your help thompsonson.

However, am I missing something, everyone is talking about SQL but I don't seem to be able to change my reports is there some additional software I need?
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Income vs Expences

Post by Nikolay »

MarcusAurelios
New MMEX User
Posts: 3
Joined: Mon Mar 24, 2014 3:12 pm
Are you a spam bot?: No

Re: Income vs Expences

Post by MarcusAurelios »

[img]http://i57.tinypic.com/2z4hi4m.jpg[/img] (Sorry not sure why this isn't working)

This is not showing on the version I downloaded (v 1.0.0.3)

Edit:

Ah I have now seen that grm is disabled in the newer versions so have downloaded an older version to see how I get on with creating my own reports.

Thanks for all assistance.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Income vs Expences

Post by Nikolay »

it's working only with beta 1.0.1.0
https://sourceforge.net/projects/moneym ... _unstable/
Post Reply