Average Spending per Category/Subcategory Report

[guanlisheng]

Hi,

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

this has been fixe by

Code: Select all

count(distinct strftime('%Y%m', tran.TRANSDATE)) as [Months Active]

on https://gist.github.com/guanlisheng/9905673 and i have mentioned it as well.

Also, half-year payments, say 15/2/2013 and 15/8/2013, will be averaged over 2 instead over 12 months.

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.


From my understanding, 99% SQL for general report should be less 50 lines.

[guanlisheng]

Regarding the Year/Quarter/Month/Week Actives count, the key here is to align TRANSDATE to the according period level.

[guanlisheng]

Let's talk more on skype (lisheng.guan) if possibly

[dimidimi]

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?

[guanlisheng]

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.

[guanlisheng]

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