Skip to main content

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!  

3 answers
  1. Feb 5, 5:20 PM

    Hey Steve, 

     

    Here is an annotated screenshot of my existing report, detailing what I'm trying to accomplish (including my existing filters). 

     

    Hey Steve, Here is an annotated screenshot of my existing report, detailing what I'm trying to accomplish (including my existing filters).

     

     

0/9000