fraction months report

17 posts
fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
Is available a report of current and lasts months for specific or all categories.

report like this:

today is 13 june 2015, and the report have to report revenue and expences group by category or payee, for any fraction of months from 1 day of month to 13.

thanks

giorgio
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I've modyfied  this report
https://github.com/moneymanagerex/gener ... st12Months

Is it OK?
Attachments
test.grm
(4.17 KiB) Downloaded 254 times
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
I tested the report. It ok. Is what I looking for.

In some months repor, does not appear some transaction.

Seems to be random error.... 

Help me
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
It may be depend on time zone.
The 'localtime' instruction should be used for date functions.
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
So, how, can I try to fix it ?

Some category with transaction, does not appear on all 12 months. (Example: montly salary and others).

Some others category on random months does not apper.


thanks

Giorgio


SQL code as follow:

Code: Select all
select case t.subcateg when-1 then ca.categname else ca.categname ||':'||sc.subcategname end
category, t.transid, t.categ, total( casestrftime('%m', date('now', 'start of month','-11 month','localtime'))
when month then amount end ) as twe, total( casestrftime('%m', date('now', 'start of month','-10 month','localtime'))
when month then amount end ) as ele, total( casestrftime('%m', date('now', 'start of month','-9 month','localtime'))
when month then amount end ) as ten, total( casestrftime('%m', date('now', 'start of month','-8 month','localtime'))
when month then amount end ) as nin, total( casestrftime('%m', date('now', 'start of month','-7 month','localtime'))
when month then amount end ) as egh, total( casestrftime('%m', date('now', 'start of month','-6 month','localtime'))
when month then amount end ) as sev, total( casestrftime('%m', date('now', 'start of month','-5 month','localtime'))
when month then amount end ) as six, total( casestrftime('%m', date('now', 'start of month','-4 month','localtime'))
when month then amount end ) as fiv, total( casestrftime('%m', date('now', 'start of month','-3 month','localtime'))
when month then amount end ) as fou, total( casestrftime('%m', date('now', 'start of month','-2 month','localtime'))
when month then amount end ) as thr, total( casestrftime('%m', date('now', 'start of month','-1 month','localtime'))
when month then amount end ) as two, total( casestrftime('%m', date('now', 'start of month','-0 month','localtime'))
when month then amount end ) as one, total(amount) asOVERALLfrom(    select         strftime('%m', TRANSDATE) as month    , c.transid,cf.BaseConvRate       , c.accountid,c.transcode       , caseifnull(c.categid, -1) when -1 then s.categid else c.categid end as
categ       , caseifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else
ifnull(c.subcategid,-1) end as subcateg       , c.payeeid       , sum((casec.categid when -1 then  splittransamount else  transamount  end)
           * (casetranscode when 'Withdrawal' then - cf.BaseConvRate else
cf.BaseConvRate end)            ) amount    fromcheckingaccount_v1 c    left joinsplittransactions_v1 s on s.transid=c.transid    left joinACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID    left joincurrencyformats_v1 cf on cf.currencyid=AC.currencyid    where transcode!= 'Transfer'    and c.status!='V'    and ac.status!='Closed'    and (date('now','start of month','-11 month','localtime') <= transdate        andtransdate < date('now', 'start of month','+1 month','localtime'))    and(strftime('%d', TRANSDATE) <= strftime('%m', 'now'))    group by month,categ, subcateg    ) t     left joincategory_v1 ca on ca.categid=t.categ    left joinsubcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg
group by categoryorder by category
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
the sql is brocken
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
THANKS

WHERE THE CODE IS BROKEN ?


CAN U HELP ME ?
Re: fraction months report

Nikolay
MMEX Developer

Posts: 2279
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I can, but I am very busy this and possible next week.
 Sorry.
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
Nikolay wrote:I can, but I am very busy this and possible next week.
 Sorry.
Thanks
Re: fraction months report

GIORGRAZ
New MMEX User

Posts: 15
Joined: Mon Jun 01, 2015 8:36 am
May u help me ?
Who is online

Users browsing this forum: No registered users and 1 guest

cron