Having trouble computing average monthly expense total per expense type for the past 12 months.
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.
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).
date is during the previous 2 months.
Get that working and then copy it 11 times and adjust the filter on each of the other 11 months.
The Count function is able to count "true" values.
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.
Right, how many of the last 12 months have more than zero expense records.