Compare Two Periods Report

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

Moderator: Renato

gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Compare Two Periods Report

Post by gpiantini »

How can I get a compare between for example January-March 2021 and January-March 2022 in the same report?
I would like to see all my categories or all my beneficiaries compared between the two periods.

Thanks a lot for your help.
MartinArmstrong
MVP MMEX User
Posts: 300
Joined: Tue Mar 02, 2021 10:24 am
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by MartinArmstrong »

I find it easiest and more flexible to handle tasks like this outside of MMEX.

As an example, for Reports | Categories | Monthly:

1. Display the report for the first year you are interested in.
2. Set the Chart dropdown to Hide.
3. Right-click in the report and 'Select all' (or press CTRL+A)
4. Press CTRL+C to copy the report data
5. In Excel (or LibreOffice Calc or Google Sheets) press CTRL+V to paste the data.

6. Repeat for the second year, pasting below the first year's data, beside it, or on another worksheet depending on what you are trying to achieve.

7. Compare the data visually, or set up formulae or conditional formatting to compare it in the way you want (for example, highlighting if >10% increase in spending for a category)
gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by gpiantini »

Thank you so much for your help!
I will try for this solution and let you know about it.
gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by gpiantini »

MartinArmstrong wrote: Fri Dec 02, 2022 2:35 pm I find it easiest and more flexible to handle tasks like this outside of MMEX.

As an example, for Reports | Categories | Monthly:

1. Display the report for the first year you are interested in.
2. Set the Chart dropdown to Hide.
3. Right-click in the report and 'Select all' (or press CTRL+A)
4. Press CTRL+C to copy the report data
5. In Excel (or LibreOffice Calc or Google Sheets) press CTRL+V to paste the data.

6. Repeat for the second year, pasting below the first year's data, beside it, or on another worksheet depending on what you are trying to achieve.

7. Compare the data visually, or set up formulae or conditional formatting to compare it in the way you want (for example, highlighting if >10% increase in spending for a category)
I tried your solution, but thers is no way to create a Report inside MMEX?
Can someone help me to create the code for this kind of report?
MartinArmstrong
MVP MMEX User
Posts: 300
Joined: Tue Mar 02, 2021 10:24 am
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by MartinArmstrong »

You wrote: "I would like to see all my categories or all my beneficiaries compared between the two periods"

But you haven't said:

1. Which report(s) you want to actually want to run the comparison on. I just guessed at Reports | Categories | Monthly.

2. How exactly you would want to see the comparison presented for the two time periods? Please could you give an clear example, otherwise people are just guessing what you need.
gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by gpiantini »

MartinArmstrong wrote: Fri Dec 02, 2022 11:45 pm You wrote: "I would like to see all my categories or all my beneficiaries compared between the two periods"

But you haven't said:

1. Which report(s) you want to actually want to run the comparison on. I just guessed at Reports | Categories | Monthly.

2. How exactly you would want to see the comparison presented for the two time periods? Please could you give an clear example, otherwise people are just guessing what you need.
Yes, you're right I'm sorry.

I post here an example of what I'd need to see in the report inside MMEX.
I would be able to select the periods to compare and select each category or beneficiary I need in the moment i Run the report script.
It has to be a bit flexible, to permit me to compare some differents periods with selected category, all of them, or only some beneficiaries.

Thanks for your time and your help.
Example.jpg
Example.jpg (32.3 KiB) Viewed 5599 times
MartinArmstrong
MVP MMEX User
Posts: 300
Joined: Tue Mar 02, 2021 10:24 am
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by MartinArmstrong »

1. Is it just Expenses that you are interested in, or is Income vs. Category relevant as well?

2. In your requirement "select each category or beneficiary", do you mean "each category or payee"? If not payee, what do you mean by beneficiary please?

3. In your requirement "select each category or beneficiary", do you mean that you want two different reports; one for categories and another for 'beneficiaries'?
gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by gpiantini »

MartinArmstrong wrote: Sat Dec 03, 2022 11:52 am 1. Is it just Expenses that you are interested in, or is Income vs. Category relevant as well?

2. In your requirement "select each category or beneficiary", do you mean "each category or payee"? If not payee, what do you mean by beneficiary please?

3. In your requirement "select each category or beneficiary", do you mean that you want two different reports; one for categories and another for 'beneficiaries'?
Martin thank you for your questions about it, I try to explain point per point:

1. No, I just to analyze and compare all the category expenses in two different periods (ex. "01/01/2021-31/03/2021" vs "01/01/2022-31/03/2022")
I would be able to choose every period I need in that moment.

2. I need to be able if I need a Category Compare Report or Payee Compare Report, always based on two different periods as before.

3. Yes, I would be able to to this, not necessary in same time/report.

I hope I clarified your doubts about it.
Thanks a lot for your help.
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: Compare Two Periods Report

Post by Renato »

My proposal would be:
In Excel, read the entries from the database .mmb and create the evaluations with the pivot functions.
Renato Forum Administrator
gpiantini
New MMEX User
Posts: 6
Joined: Fri Dec 02, 2022 12:07 pm
Are you a spam bot?: No

Re: Compare Two Periods Report

Post by gpiantini »

Renato wrote: Sat Dec 03, 2022 8:16 pm My proposal would be:
In Excel, read the entries from the database .mmb and create the evaluations with the pivot functions.
I don't know how to do this.. if you can help me but I prefere generate a report directly into MMEX.
Post Reply