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.guanlisheng wrote:you can view the report like other normal reports directly within MMEX if you are using the latest binary for window.
Average Spending per Category/Subcategory Report
Moderator: Renato
Re: Average Spending per Category/Subcategory Report
-
- 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
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>
Re: Average Spending per Category/Subcategory Report
Did not work eitherNikolay 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>
-
- 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
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
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
- Renato
- MVP User
- Posts: 674
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: Average Spending per Category/Subcategory Report
Hello Nikolay
Gratulation, its work after insert mmex.exe in registry
Please insert in the title class = "text-right" in numeric colums
http://localhost:8080/ still results in 404 Page not found
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>
Renato Forum Administrator
Re: Average Spending per Category/Subcategory Report
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: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
Code: Select all
var elements= document.getElementsByClassName("money");
Code: Select all
var elements= document.querySelectorAll(".money");
EDIT: I will check it on my home's PC (Win7Pro 64bit, IE11) as soon as a get home and let you know.
-
- 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
Updated report.
Lua, js and style used.
Lua, js and style used.
- Attachments
-
- Average Spending per Category Active Months.grm
- (2.29 KiB) Downloaded 496 times
Re: Average Spending per Category/Subcategory Report
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!
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!
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
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
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)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Average Spending per Category/Subcategory Report
Hi danyall!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!
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.
Re: Average Spending per Category/Subcategory Report
Hi guanlisheng!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
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
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
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.
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.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Average Spending per Category/Subcategory Report
guanlisheng hi!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.
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
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.
- Attachments
-
- AveragesPerCategSubcategOneSubqueryLess.zip
- (1001 Bytes) Downloaded 565 times
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
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.
Regarding the SQL line #, if it is over 50 lines, i can 90% guarantee that it is problematic.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Average Spending per Category/Subcategory Report
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.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.
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
Hi,
on https://gist.github.com/guanlisheng/9905673 and i have mentioned it as well.
From my understanding, 99% SQL for general report should be less 50 lines.
this has been fixe byThis 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
Code: Select all
count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active]
A little confusion about your definition upon 'Active Months'. Intuitively, the correct answer should be 2 here. therefore, i still don't get your point here. would you please provide more example to illustrate the calculation logic here.Also, half-year payments, say 15/2/2013 and 15/8/2013, will be averaged over 2 instead over 12 months.
From my understanding, 99% SQL for general report should be less 50 lines.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
Regarding the Year/Quarter/Month/Week Actives count, the key here is to align TRANSDATE to the according period level.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
Let's talk more on skype (lisheng.guan) if possibly
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
Re: Average Spending per Category/Subcategory Report
guanlisheng hi-
You're right... The term 'active' months does not completely reflect the concept I have in mind Specifically, what I want to accomplish with this report is to know what I spend per Category/Subcategory on a monthly basis.
For payments made on a monthly basis counting the number of transactions, such as count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active], would work just fine. For other payments that occur yearly, quarterly etc. simply counting the number of transactions would not yield to a correct average. For example, I pay my car insurance (Category: Vehicles, Subcategory: Insurance) in two installments:
2014/02/24 (first installment for the period 2014/02 – 2014/08): 324.15 Euros
2014/08/24 (first installment for the period 2014/02 – 2015/08): 335.24 Euros
So my monthly average here should be 659.39 / 12 = 54.95 whereas by simply counting the number of withdrawals will give me 329.70. Hope this makes sense. I will try to contact you through Skype what timezone are you in?
You're right... The term 'active' months does not completely reflect the concept I have in mind Specifically, what I want to accomplish with this report is to know what I spend per Category/Subcategory on a monthly basis.
For payments made on a monthly basis counting the number of transactions, such as count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active], would work just fine. For other payments that occur yearly, quarterly etc. simply counting the number of transactions would not yield to a correct average. For example, I pay my car insurance (Category: Vehicles, Subcategory: Insurance) in two installments:
2014/02/24 (first installment for the period 2014/02 – 2014/08): 324.15 Euros
2014/08/24 (first installment for the period 2014/02 – 2015/08): 335.24 Euros
So my monthly average here should be 659.39 / 12 = 54.95 whereas by simply counting the number of withdrawals will give me 329.70. Hope this makes sense. I will try to contact you through Skype what timezone are you in?
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
Still take your installments as example, i think there should be 12 transactions in database instead of only 2. so the monthly average IS 659.39 / 12 = 54.95. (there must be some mis-spell in your case).
To this case, it depends how you record the installments. From my understanding it should be repeat transaction which will happen monthly and result in 12 separate transactions in six months.
I am living in China and the timezone is GMT+8.
To this case, it depends how you record the installments. From my understanding it should be repeat transaction which will happen monthly and result in 12 separate transactions in six months.
I am living in China and the timezone is GMT+8.
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)
-
- Developer
- Posts: 381
- Joined: Wed Dec 21, 2011 5:58 am
- Are you a spam bot?: No
- Location: China
- Contact:
Re: Average Spending per Category/Subcategory Report
One correction to your statement, i think it's counting the periods that the given transactions happened.
For payments made on a monthly basis counting the number of transactions
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Developer of MoneyManagerEX (http://moneymanagerex.org)