cashflow report

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

Moderator: Renato

Post Reply
SWPanton
Senior MMEX User
Posts: 21
Joined: Wed May 25, 2022 9:47 am
Are you a spam bot?: No

cashflow report

Post by SWPanton »

Hello all
I have adapted the account-account forecast for my cashflow and tried to extend it to 24 months. For some reasons from month 12 the data is repeating itself and I'm new to SQL so cannot figure out why. It might be todo with the lua or template, neither of which I fully understand.

so three questions:
1. What do I need to change in the attached to give me correct data after 12 months.

2. This is for a specific account so is it possible to do this for several accounts on the same report such as for a landscape print as opposed to different reports.

3. Is there somewhere online I can learn about Lua and templates

regards Simon
Attachments
Cashflow Bills Account.grm
(4.16 KiB) Downloaded 271 times
mikhailo2608ilaol
New MMEX User
Posts: 17
Joined: Tue Feb 09, 2021 6:08 pm
Are you a spam bot?: No

Re: cashflow report

Post by mikhailo2608ilaol »

It appears to me that part of your issue is that the BILLSDEPOSITS_V1 table that containing the Recurring Transactions has transaction dates that only go out no longer than one year from the current date. Another challenge is that once you use the TRANSDATE in the BILLSDEPOSIT_V1 table in a row in the output for your SQL statement the TRANSDATE is not updated - which is to say that if you have a recurring payment that occurs monthly then only the payment for the first month will be reflected in the report but not for any subsequent months in the reporting period. When I downloaded your report and ran it in my MMEX database under TEST in the SQL tab of the General Reports Manager the SQL produced 15 lines of output which corresponded with the 15 recurring transactions I had in my MMEX database. I see the problem but cannot offer any solution as my SQL skills are minimal - I can spell SQL but that's about it.
SWPanton
Senior MMEX User
Posts: 21
Joined: Wed May 25, 2022 9:47 am
Are you a spam bot?: No

Re: cashflow report

Post by SWPanton »

Hi there many thanks for your input. I noticed that after my posting the dates were repeating so made a correction hoping that was the solution but it appears not. So your hypothesis that recurring transactions are only reflected in the first month would make sense accept the first few months give the correct balances when compared to the cashflow daily and monthly figures then correct again after Dec 2023

Where the cashflow goes wrong is from Jan to dec 2023, after that is correct. Does anybody have any other ideas.

Also need to know how to combine reports so that are on same page as opposed to different reports.

regards Simon
Attachments
Cashflow Bills Account.grm
(4.18 KiB) Downloaded 279 times
whall3y
MMEX Developer
Posts: 343
Joined: Fri Aug 21, 2020 5:44 pm
Are you a spam bot?: No

Re: cashflow report

Post by whall3y »

SWPanton wrote: Tue Jun 14, 2022 7:52 pm
Not directly solving your GRM development issues.... but I have added an extra CashFlow report to the MMEX standard report set that hopefully meets your needs. It can be found in the latest beta https://ci.appveyor.com/project/moneyma ... ymanagerex

Let me know if you find any issues.
Post Reply