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

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

Moderator: Renato

Post Reply
spoker
New MMEX User
Posts: 14
Joined: Wed Nov 09, 2016 11:24 pm
Are you a spam bot?: No

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

Post 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? 
spoker
New MMEX User
Posts: 14
Joined: Wed Nov 09, 2016 11:24 pm
Are you a spam bot?: No

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

Post by spoker »

Nobody tried this before? 
Can you help me get started on this, using maybe another report as a base?
spoker
New MMEX User
Posts: 14
Joined: Wed Nov 09, 2016 11:24 pm
Are you a spam bot?: No

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

Post by spoker »

Anybody care to help get me started?


Thanks
spoker
New MMEX User
Posts: 14
Joined: Wed Nov 09, 2016 11:24 pm
Are you a spam bot?: No

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

Post 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
spoker
New MMEX User
Posts: 14
Joined: Wed Nov 09, 2016 11:24 pm
Are you a spam bot?: No

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

Post by spoker »

Post Reply