Nesting of aggregate function is not allowed

Q&A
, ,
1

Hi Team,

I’m encountering an issue with nested aggregates while trying to calculate a compliance rate. Here’s what I’m trying to accomplish:

First, I need to calculate the completion rate:
    Completion Rate = sum(swcl_daily_compliant_ct) / sum(swcl_daily_total_day_ct)

Then, I want to calculate the compliance rate:
    Numerator: Sum of swcl_daily_compliant_ct (but only when completion rate ≥ 0.9)
    Denominator: Total count of trainer_id
    Compliance Rate = Numerator / Denominator

I’ve attempted to use this formula:

sumif(sum({swcl_daily_compliant_ct}),sum({swcl_daily_compliant_ct})/sum({swcl_daily_total_day_ct})>=0.9)/count({site_trainer_id})
However, I’m getting a nested aggregate error. Could you please suggest an alternative approach to achieve this calculation?

image
image1953×223 28.1 KB

2

Hi @Li_Yang ,

you can try:

sumif({swcl_daily_compliant_ct},sum({swcl_daily_compliant_ct})/sum({swcl_daily_total_day_ct})>=0.9)/count({site_trainer_id})

If this works the problem was you want to sum (sumif) a sum (the sum(swcl_daily_compliant_ct)).

Best regards,
Nico

3

@Nico However, I’m still encountering the “nesting of aggregate function” error with both suggested solutions. Would you be able to recommend any alternative formulas that might work?

nestingOfAggregate_error
nestingOfAggregate_error1576×495 52.2 KB

4

Hi @Li_Yang ,

the solution depends on the granularity of your data.

I case you can calculate the Completion Rate per line in your dataset (=> no aggregation needed) you can remove the sums:

sumif(
	{swcl_daily_compliant_ct},
	({swcl_daily_compliant_ct})/({swcl_daily_total_day_ct})>=0.9
	)/count({site_trainer_id})

If you need more lines to be aggregated to calculate the correct Completion Rate, you can use level aware calculations to sum the values in a defined partition: Using level-aware calculations in Amazon QuickSight - Amazon QuickSight

Best regards,
Nico

5

Thanks for your suggestion!

6

Hi @Li_Yang,
It’s been awhile since last communication on this thread, were you able to find a work around from the solutions provided above or did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

7

@Brett Thanks for reaching out. I did some necessary calculation in the SQL script which to solve the issue. I appreciate Nico’s insights during this process. The ticket can now be closed.