Average Spending per Category/Subcategory Report

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

Moderator: Renato

dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

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

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

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
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

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

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

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
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: Average Spending per Category/Subcategory Report

Post by Renato »

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
Renato Forum Administrator
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

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

Re: Average Spending per Category/Subcategory Report

Post by Nikolay »

Updated report.
Lua, js and style used.
Attachments
Average Spending per Category Active Months.grm
(2.29 KiB) Downloaded 489 times
danyall
New MMEX User
Posts: 5
Joined: Mon Mar 31, 2014 3:54 pm
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by danyall »

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
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Average Spending per Category/Subcategory Report

Post by guanlisheng »

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
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
dimidimi
Senior MMEX User
Posts: 37
Joined: Tue Mar 04, 2014 10:46 am
Are you a spam bot?: No

Re: Average Spending per Category/Subcategory Report

Post by dimidimi »

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.
Post Reply