Page 1 of 1

Income vs. expense report (monthly) WITH transfers to loan

Posted: Tue Nov 22, 2016 8:14 pm
by spoker
Hello,

I would love a income vs. expense report, like the one that is available in mmex 1.3.0rc2, but with transfers to a loan account added to the expenses. Tbese transfers are usually the repayment of the principal of a mortgage and represent money that is no longer available.

I, and i think a lot of other people to, would love this.

Can I get this done with a custom report? 

Re: Income vs. expense report (monthly) WITH transfers to lo

Posted: Mon Dec 05, 2016 3:36 pm
by spoker
Nobody tried this before? 
Can you help me get started on this, using maybe another report as a base?

Re: Income vs. expense report (monthly) WITH transfers to lo

Posted: Wed Dec 28, 2016 4:09 pm
by spoker
Anybody care to help get me started?


Thanks

Re: Income vs. expense report (monthly) WITH transfers to lo

Posted: Wed Dec 28, 2016 9:37 pm
by spoker
I tried it myself based on another report in the github repository (https://github.com/moneymanagerex/gener ... nueByMonth)
It's not really 'clean' and contains hardcoded accountID's, but it works.

Any tips on how to clean this up so it looks like the built-in revenue vs expenses charts?

Code: Select all

select 
    Periode,
    sum(Deposit) as Inkomsten,
   sum(Transfer) as Afbetaling,
    sum(Withdrawal) as Uitgave,
    round(sum(Deposit) + sum(Withdrawal) + sum(Transfer),2) as Totaal
from (  
    select 
        strftime('%Y-%m', TRANSDATE) as Periode,
        case
            when transcode = 'Deposit' then transamount
            else 0
        end as Deposit,
        case
          when transcode = 'Withdrawal' then -transamount
          else 0
        end as Withdrawal,
      case
         when transcode = 'Transfer' and toaccountid=('19') then -transamount
         when transcode = 'Transfer' and toaccountid=('20') then -transamount
         else 0
      end as Transfer
        --,*
    from
        checkingaccount_V1
    where
        TRANSDATE > date('now', 'start of month','-4 year','localtime')
        and status <>'V'
)
group by periode
order by periode asc

Re: Income vs. expense report (monthly) WITH transfers to lo

Posted: Mon Jan 02, 2017 10:59 am
by spoker