4 Replies Latest reply on Jun 11, 2012 1:30 PM by Chris Rasmussen

# why can't I aggregate an aggregate?

Why can't I aggregate an aggregate?

I have a beautiful table that shows survey completion rates by group by week.  Each cell is showing me a percent complete for that group for a specific week.

What I want is the ability to show an average percent completion rate for each week, but this would mean I'd need to have a calculation that looks like:

avg([percent_complete_of_mailed]), and I am given the error message that I can't do an aggregate of an aggregate.

The [percent_complete_of_mailed] is a calculation of sum([completes])/[number_mailed], so I can't do avg(sum([completes])/[number_mailed]) either!

[number_mailed] is a calculation which is a sum of records with certain attributes.

How can I get this average?  any ideas?

• ###### 1. Re: why can't I aggregate an aggregate?

Hi Chris,

Would the following give the correct answer?

[percent_complete_of_mailed]/sum([Number of Records])

-Tracy

• ###### 2. Re: why can't I aggregate an aggregate?

Hi Tracy -

Good idea, and the calculation is valid... but how to use?  It's like I want a grand total row, but instead of the grand total, I want this average put there instead.  Your calculation would work b/c it would know the number of records in the column.  However, if I put your calculation, say, on the level of detail, it's messed up because it doesn't know which number of records to use.

1 of 1 people found this helpful
• ###### 3. Re: why can't I aggregate an aggregate?

Hi Chris,

This sounds like it's calling for a dashboard with two worksheets - one with the detail, and the second with the overall average per week. Does that make sense?

Jonathan

• ###### 4. Re: why can't I aggregate an aggregate?

Hi -

Thank you for all your responses.  Ultimately, what did work was to use window_avg([percent_completeOfTotalMailed],first(),last()).  I used this on a level of detail shelf so one could at least hover and see the average for that week.

Thanks!

-Chris