Summary of a Summary

[benji]

I have a table of expenses and a report layout that summarizes them by month and category. At the end of each month is a total of all expenses in the month. I want to take those monthly totals and average them over however many months are in the found set.

For instance, if I perform a find of records from 3/1/08 to 7/31/08, and display them in my report layout, I get totals for March, April, May, June, and July. But how do I get FM to automatically total those totals and know that there are 5 months in the found set and therefore divide the total by 5? It seems to me there would be some way to find unique month values in the found set and count them, but I'm not sure how. Or maybe I'm not going about it right…

Any thoughts would be great.

[comment]

It seems to me there would be some way to find unique month values in the found set and count them

See here:

http://fmforums.com/forum/showpost.php?post/297916/

Another option is to calculate the number of months from your report Start and End dates (assuming these are available in global fields).

[benji]

That's a good idea, actually, because then I could probably calculate fractional months too, if my report ran halfway through a month. But, yeah, I hadn't gotten that far as to use globals. I was just doing a Find Request with a range (…) in the date field. So maybe the solution you referenced there would work for now. It's a good trick for finding unique values.

Thanks.