fraction months report

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

Moderator: Renato

GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

fraction months report

Post by GIORGRAZ »

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
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: fraction months report

Post by Nikolay »

I've modyfied  this report
https://github.com/moneymanagerex/gener ... st12Months

Is it OK?
Attachments
test.grm
(4.17 KiB) Downloaded 685 times
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

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
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: fraction months report

Post by Nikolay »

It may be depend on time zone.
The 'localtime' instruction should be used for date functions.
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

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
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: fraction months report

Post by Nikolay »

the sql is brocken
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

THANKS

WHERE THE CODE IS BROKEN ?


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

Re: fraction months report

Post by Nikolay »

I can, but I am very busy this and possible next week.
 Sorry.
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

Nikolay wrote:I can, but I am very busy this and possible next week.
 Sorry.
Thanks
GIORGRAZ
New MMEX User
Posts: 19
Joined: Mon Jun 01, 2015 2:36 pm
Are you a spam bot?: No

Re: fraction months report

Post by GIORGRAZ »

May u help me ?
Post Reply