Average Spending per Category/Subcategory Report

dimidimi · Post by **dimidimi** » Fri Mar 21, 2014 12:33 pm

Hi everyone!

Here's my first attempt to build a custom report. I'm pretty sure it can be fine tuned, at least as far as the output is concerned. Please note that in the SQL statement I have explicitly excluded PayeeId 15 (

Code: Select all

payeeid <> 15

) as it represent some sort of temporary items in my setup.

Hope you find it useful. If you have any suggestions feel free to let me know
Dimitrios

PS. I haven't uploaded to the General Report collection on Github yes as the report lacks the Lua section and also I did not know how to create a .htt file.

Post by **Nikolay** » Mon Mar 24, 2014 2:32 pm

That super if users can create reports. Seems GRM concept is working!

Dima,

try this template:

Code: Select all

<!DOCTYPE html><h3><TMPL_VAR REPORTNAME></h3>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title><TMPL_VAR REPORTNAME></title>
    <link href = "master.css" rel = "stylesheet">
</head>
<body>
<TMPL_VAR TODAY><hr>
<table class = "table">
<thead>
    <tr>
        <th>Category</th>
        <th>Subcategory</th>
        <th>Total Spend</th>
        <th>Months Active</th>
        <th>Average Spending</th>
    </tr>
</thead>
    <TMPL_LOOP NAME=CONTENTS>
        <tr>
        <td><TMPL_VAR "Category"></td>
        <td><TMPL_VAR "Subcategory"></td>
        <td class="money"><TMPL_VAR "Total Spend"></td>
        <td><TMPL_VAR "Months Active"></td>
        <td class="money"><TMPL_VAR "Average Spending"></td>
        </tr>
    </TMPL_LOOP>
</table>
<TMPL_LOOP ERRORS>
    <hr>
    <TMPL_VAR ERROR>
</TMPL_LOOP>
</body>
<script type="text/javascript">
  function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
  var elements= document.getElementsByClassName("money");
  for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
</script>
</html>

guanlisheng · Post by **guanlisheng** » Tue Mar 25, 2014 3:55 am

Dimitrios, welcome to General Report board and your SQL is perfect. one screenshot is attached.

屏幕快照 2014-03-25 上午11.48.21.png: (132.77 KiB) Downloaded 1149 times

dimidimi · Post by **dimidimi** » Tue Mar 25, 2014 7:34 pm

guanlisheng wrote:Dimitrios, welcome to General Report board and your SQL is perfect. one screenshot is attached.
屏幕快照 2014-03-25 上午11.48.21.png

I'm glad you liked it guanlisheng

I like your layout a lot better! I tried using Nikolay's template above, but it looks like the javascript portion did not have any effect on the numerical data; i.e. I did not get any currency formatting

. Btw, is the "Lua" section mandatory in order to fork a report to the Github?

EDIT: I experimented a little bit the Nikolay's template and noticed that table formatting was applied after I included the full path name to the reference of master.css. However, I amm still unable to make the javascript portion work. I would greatly appreciate any pointers.

guanlisheng · Post by **guanlisheng** » Wed Mar 26, 2014 2:55 am

Hi Dimitrios, the general report on windows platform is still problematic to load related css & js resource files.
The work around is to visit http://localhost:8080/ with your chrome browser.

Lua section is not mandatory and it just help to revise the dataset returned by SQL.

Post by **Nikolay** » Wed Mar 26, 2014 8:37 am

try newest vertion

mmex_svn6254_x64_portable.zip

https://sourceforge.net/projects/moneym ... unstable/?

dimidimi · Post by **dimidimi** » Wed Mar 26, 2014 12:12 pm

Hi Nikolay! I used the latest unstable build you proposed and tried the workaround by visiting http://localhost:8080/, using three different browsers and still was not able to get the currency formatting to work. Let me know if you want me to send you the source of the generated html report. Thanks!

guanlisheng · Post by **guanlisheng** » Wed Mar 26, 2014 12:20 pm

you can view the report like other normal reports directly within MMEX if you are using the latest binary for window.

Post by **Renato** » Wed Mar 26, 2014 1:42 pm

Sorry Guys

I have the same Problem, its not work, the script is not executed, numbers are not formatted.
http://localhost:8080/ results on 404 Page not found

Post by **Nikolay** » Wed Mar 26, 2014 1:46 pm

download last dev release
https://sourceforge.net/projects/moneym ... _unstable/?

if report started from navigation panel (and mmex still started) the page localhost:8080 will be available

dimidimi · Post by **dimidimi** » Wed Mar 26, 2014 1:59 pm

guanlisheng wrote:you can view the report like other normal reports directly within MMEX if you are using the latest binary for window.

Hi again! I am able to see the report directly within MMEX but the script which formats the currency fields (class="money") does not seem to get executed.

Post by **Nikolay** » Wed Mar 26, 2014 2:10 pm

Try this:

Code: Select all

                        <td class="money text-right"><TMPL_VAR Base></td>

</body>
<script type="text/javascript">
  function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
  var elements= document.getElementsByClassName("money");
  for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
</script>
</html>

dimidimi · Post by **dimidimi** » Wed Mar 26, 2014 4:11 pm

Nikolay wrote:Try this:

Code: Select all

                        <td class="money text-right"><TMPL_VAR Base></td>

</body>
<script type="text/javascript">
  function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
  var elements= document.getElementsByClassName("money");
  for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
</script>
</html>

Did not work either

Post by **Nikolay** » Wed Mar 26, 2014 5:10 pm

Please check windows registry
HKCU; Subkey: "Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION";
ValueType: dword;
ValueName: "mmex.exe";
ValueData: "9000";

For more info
http://msdn.microsoft.com/en-us/library ... _emulation

Post by **Renato** » Wed Mar 26, 2014 8:04 pm

Hello Nikolay

Gratulation, its work after insert mmex.exe in registry

Please insert in the title class = "text-right" in numeric colums

Code: Select all

thead>
    <tr>
        <th>Category</th>
        <th>Subcategory</th>
        <th class = "text-right">Total Spend</th>
        <th class = "text-right">Months Active</th>
        <th class = "text-right">Average Spending</th>
    </tr>
</thead>
<tbody>
    <TMPL_LOOP NAME=CONTENTS>
        <tr>
        <td><TMPL_VAR "Category"></td>
        <td><TMPL_VAR "Subcategory"></td>
        <td class = "money, text-right"><TMPL_VAR "Total Spend"></td>
        <td class = "text-right"><TMPL_VAR "Months Active"></td>
        <td class = "money, text-right"><TMPL_VAR "Average Spending"></td>
        </tr>
    </TMPL_LOOP>

http://localhost:8080/ still results in 404 Page not found

dimidimi · Post by **dimidimi** » Thu Mar 27, 2014 6:56 am

Nikolay wrote:Please check windows registry
HKCU; Subkey: "Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION";
ValueType: dword;
ValueName: "mmex.exe";
ValueData: "9000";

For more info
http://msdn.microsoft.com/en-us/library ... _emulation

After adding the above to the registry and a little bit of tweaking it worked in my job's PC (WinXP, IE8). In the currency formatting script I changed:

Code: Select all

var elements= document.getElementsByClassName("money");

to:

Code: Select all

var elements= document.querySelectorAll(".money");

Thanks!

EDIT: I will check it on my home's PC (Win7Pro 64bit, IE11) as soon as a get home and let you know.

Post by **Nikolay** » Sat Mar 29, 2014 6:40 pm

Updated report.
Lua, js and style used.

danyall · Post by **danyall** » Mon Mar 31, 2014 5:38 pm

Hi Dimitri!

I can get rows with the query. Am I doing something wrong?

I'am using this query and when I test it I get 0 rows.

SELECT transcount.categname as Category,
transcount.subcategname as Subcategory,
sum(totalamounts.sumamounts) as [Total Spend],
count(*) as [Months Active],
sum(totalamounts.sumamounts) / count(*) as [Average Spending]
FROM (
SELECT mo, categname, subcategname, nofentries
FROM [SUBCATEGORY_V1] stab, [CATEGORY_V1] ctab, (
SELECT mo, cid, sid, count(*) as nofentries FROM (
SELECT strftime('%m', TRANSDATE) mo, CATEGID cid, SUBCATEGID sid, TRANSAMOUNT amnt
FROM [CHECKINGACCOUNT_V1]
where transcode = 'Withdrawal'
and payeeid <> 15
and categid <> -1
union
SELECT strftime('%m', a.TRANSDATE) mo, b.CATEGID, b.SUBCATEGID, b.SPLITTRANSAMOUNT
FROM [CHECKINGACCOUNT_V1] a, [SPLITTRANSACTIONS_V1] b
where a.transcode = 'Withdrawal'
and a.payeeid <> 15
and a.categid = -1
and a.transid = b.transid
)
group by mo, cid, sid
) totalentries
where stab.subcategid = totalentries.sid
and ctab.categid = totalentries.cid
) transcount
INNER JOIN (
select mo, categname, subcategname, sumamounts
from [SUBCATEGORY_V1] stab, [CATEGORY_V1] ctab, (
SELECT mo, cid, sid, sum(amnt) as sumamounts FROM (
SELECT strftime('%m', TRANSDATE) mo, CATEGID cid, SUBCATEGID sid, TRANSAMOUNT amnt
FROM [CHECKINGACCOUNT_V1]
where transcode = 'Withdrawal'
and payeeid <> 15
and categid <> -1
union all
SELECT strftime('%m', a.TRANSDATE) mo, b.CATEGID, b.SUBCATEGID, b.SPLITTRANSAMOUNT
FROM [CHECKINGACCOUNT_V1] a, [SPLITTRANSACTIONS_V1] b
where a.transcode = 'Withdrawal'
and a.payeeid <> 15
and a.categid = -1
and a.transid = b.transid
)
group by mo, cid, sid
) totalentries
where stab.subcategid = totalentries.sid
and ctab.categid = totalentries.cid
) totalamounts
ON transcount.mo = totalamounts.mo
and transcount.categname = totalamounts.categname
and transcount.subcategname = totalamounts.subcategname
GROUP BY transcount.categname, transcount.subcategname
;

I had more than 200 transaction and many categories loaded, but mostly without sub category.

Thanks a lot!

guanlisheng · Post by **guanlisheng** » Tue Apr 01, 2014 12:50 am

No time to review your SQL carefully but to be honest, your code is too long and complex.
These unions are not needed at all and i have posted one simplified one days ago.

https://gist.github.com/guanlisheng/9905673

dimidimi · Post by **dimidimi** » Wed Apr 02, 2014 10:48 am

danyall wrote:Hi Dimitri!

I can get rows with the query. Am I doing something wrong?

I'am using this query and when I test it I get 0 rows.

SELECT transcount.categname as Category,
transcount.subcategname as Subcategory,
sum(totalamounts.sumamounts) as [Total Spend],
count(*) as [Months Active],
sum(totalamounts.sumamounts) / count(*) as [Average Spending]
<omitted part>
ON transcount.mo = totalamounts.mo
and transcount.categname = totalamounts.categname
and transcount.subcategname = totalamounts.subcategname
GROUP BY transcount.categname, transcount.subcategname
;

I had more than 200 transaction and many categories loaded, but mostly without sub category.

Thanks a lot!

Hi danyall!

I tested the above query in my database, which does not contain as many 'Withdrawal' transactions as yours and it does return the correct result set. Note however, that the query returns all 'Withdrawal' transactions that have been assigned to both a Category and a Subcategory. If you want to get those that are assigned to a Category regardless of a Subcategory you need to tweak it so that you omit the subcategory elements from the joins and the unions. Also note the in my implementation I have excluded payeeid 15 (payeeid <> 15), as it does not make sense in the way I have setup my finances.

Let me know if you need more hep with this
Dimitrios

PS. Also note that the above does not calculate the average correctly. If, for example, you have transactions that repeat on the same month but in different years, they will be counter as one which obviously is not correct. I have implemented a correct version by tweaking the database schema using views. If would be extremely complex to make one single query for getting everything.

dimidimi · Post by **dimidimi** » Wed Apr 02, 2014 11:13 am

guanlisheng wrote:No time to review your SQL carefully but to be honest, your code is too long and complex.
These unions are not needed at all and i have posted one simplified one days ago.

https://gist.github.com/guanlisheng/9905673

Hi guanlisheng!

I don't know how I missed that

You're absolutely right about the unions, the ifnull() implementation is so much easier to follow! Thanks! However, I believe that the query does not calculate the average correctly. If, for example, you have transactions that repeat on the same month but in different years, they will be counted as one which obviously is not correct. I have implemented a correct version by tweaking the database schema using views. If would be extremely complex to make one single query for getting everything. I will give it a try though and see how it would work

Thanks again
Dimitrios

guanlisheng · Post by **guanlisheng** » Thu Apr 03, 2014 1:16 am

Hi Dimitrios, thanks for pointing active months count issue and i just post a in-place fix on gist.

Regarding the SQL aspect for general report, it should be less than 50 lines which meant we can read all sql in on screen. and better to write code in gist and just post on link in forum.

dimidimi · Post by **dimidimi** » Thu Apr 03, 2014 10:08 am

guanlisheng wrote:Hi Dimitrios, thanks for pointing active months count issue and i just post a in-place fix on gist.

Regarding the SQL aspect for general report, it should be less than 50 lines which meant we can read all sql in on screen. and better to write code in gist and just post on link in forum.

guanlisheng hi!

I have managed to make the entire output recordset into a single SQL query. It is somewhat larger that 50 lines, 78 lines to be exact, but what bothers the most is how the database engine would handle a particular subquery; i.e. the one which gets all withdrawals for both normal and splitted transactions. This is actually the one you fine tuned in your version of report:

Code: Select all

(select ifnull(split.categid, tran.categid) as categid,
          ifnull(split.subcategid, tran.subcategid) as subcategid,
             tran.transdate as transdate,
             ifnull(split.splittransamount, tran.transamount) as transamount,
             tran.notes
     from checkingaccount_v1 as tran
     left join splittransactions_v1 as split
     on tran.transid = split.transid
     where tran.payeeid != 15
     and tran.subcategid <> 66
     and tran.transcode = 'Withdrawal'
     order by categid,subcategid,transdate
) categ_subcateg_withdrawals
group by categ_subcateg_withdrawals.categid, categ_subcateg_withdrawals.subcategid

It looks to me that the 3rd occurrence might be redundant, but I haven't figured a way to remove it yet. At any rate, though, this is the idea for computing a correct average which also covers for the case in which you pay expenses not only in a monthly base, but also bi-montly, 4 times/year, twice a year etc.

If you find an easier to way to do this please let me know

Thanks!
Dimitrios

EDIT:
OK... I managed to eliminate the 3rd occurrence of the categ_subcateg_withdrawals subuquery, improving the performance and simplicity of the overall statement. Please check the attached SQL.

guanlisheng · Post by **guanlisheng** » Thu Apr 03, 2014 12:44 pm

I don't understand what is your problem here since i think the SQL in gist already resolves requirements to retrieve active month (or other period) count and total amount.

Regarding the SQL line #, if it is over 50 lines, i can 90% guarantee that it is problematic.

dimidimi · Post by **dimidimi** » Thu Apr 03, 2014 5:03 pm

guanlisheng wrote:I don't understand what is your problem here since i think the SQL in gist already resolves requirements to retrieve active month (or other period) count and total amount.

Regarding the SQL line #, if it is over 50 lines, i can 90% guarantee that it is problematic.

guanlisheng, the problem with the query is that it does not correctly calculate the number of months between the first and the last withdrawal for e particular (categid, subcateg) pair. Based of the month portion of the transdate, it counts the number of occurrences. This is incorrect as two payments on the same month but different year, say 11/2/2013 and 21/2/2014, will be averaged on 1 instead of 12 months. Also, half-year payments, say 15/2/2013 and 15/8/2013, will be averaged over 2 instead over 12 months. Hope this makes sense. Given that I was not able to implement two queries and a more appropriate logic in the Lua section, the correct calculation has to be performed in a single query that has to be more that 50 lines long.

MMEX Discussion Forum

Average Spending per Category/Subcategory Report

Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report

Re: Average Spending per Category/Subcategory Report