I'm attempting to create a report showing the average hours spent in meetings per calendar week. It's an Activities with Accounts report, filtered to the current calendar year. The hours value is a formula field converting the standard Duration (Minutes) field into an hour numeric value. It's grouped by Date, Grouped by Calendar Week and the Duration (Hours) is Sum = TRUE (Summary "Sum" is checked).
Yes, on December 31st of this year, I could make a simple summary formula dividing all Event Hours from the past year by 52 weeks. However, I'd like to be able to see on a rolling basis, as weeks pass throughout the year, what the running average is: Grand total Duration (Hours) SUM / # of weeks that have happened so far this year (grouping count). For example, it's the 5th week of the year. Week 1: 2 hrs of meetings, week 2: 3 hrs, week 3: 4 hrs, week 4: 5 hrs, week 5: 6 hrs.
20 hrs / 5 weeks = avg 4 hrs per week so far this year.
I've built a Summary Formula Column that is "valid", but always returns 1, regardless of the report date range. Displaying at Grand Total only.
DURATIONHOURS:SUM / PARENTGROUPVAL(DURATIONHOURS:SUM, GRAND_SUMMARY)
Does anyone have a similar example I could adapt where you're using a grouping count?
Thank you!
Hey Steve,
Here is an annotated screenshot of my existing report, detailing what I'm trying to accomplish (including my existing filters).