# Having trouble computing average monthly expense total per expense type for the past 12 months.

• 0
• Question
• Updated 3 years ago
This is more complex than simply using a summary report and showing the averages.    The reason being is that I don't want the average based on the # of records for each expense type.  I want the average based on the number of months in the past 12 months that have expense data in it.   For example, if Senior Consultant expense type has 27 records in the past 12 months for a total expense amount of \$50,000, but those records are all from March, May, and August, then the calculation should be \$50,000 / 3, not \$50,000 / 27.
• 200 Points

Posted 3 years ago

• 0
• 72,096 Points
You will need to do the calculation of the Average with more effort.

You will need to make 12 Summary count fields to count the # of expenses in each of the previous 12 months.

for example a filter on the Summary field named [# of Expenses Month C-2] (ie current month - 2).

The filter would be where

date is during the previous 2 months.

and date is not during the previous 1 month

set that field property to treat blank a

s zero.

Get that working and then copy it 11 times and adjust the filter on each of the other 11 months.

Then to make your Average calculation, it will be

[Total Expenses last 12 months] /

Count(

[# of Expenses Month C-1] > 0,

[# of Expenses Month C-2] > 0,

[# of Expenses Month C-3] > 0,

[# of Expenses Month C-4] > 0,

etc

[# of Expenses Month C-12] > 0)

The Count function is able to count "true" values.

• 200 Points
Got it.  Thanks. I kept thinking that counting the # of expenses was not the answer but I can see now that I needed to factor in when the count is greater than 0.
• 72,096 Points
Right, how many of the last 12 months have more than zero expense records.