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

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
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.
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
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.









 
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Right, how many of the last 12 months have more than zero expense records.