# Nesting of aggregrate functions is not allowed error

I am trying to calculate the lower limit for success rate with the following calculation
avg({Success Rate})- 3*(stdev({Success Rate}))

I am getting the following error: nesting of aggregrate functions is not allowed

The success rate is calculated using the formula below
count(ifelse(transactioncode = 14 OR transactioncode = 17, ‘Success’, NULL)) / count(transactioncode)

How do I go about calculating the upper limit?

Hi @Ciku,

You can’t use the count function inside avg. That’s what nesting of aggregate functions mean (they’re both aggregate functions). To get around this, change your count function to countOver.

Okay. But can I add a condition ie transactioncode = 14 OR transactioncode = 17 when I am using countover?

@Ciku
Yes, you can but you need to figure out what partition to use in your countOver function.

``````countOver(ifelse(transactioncode = 14 OR transactioncode = 17, Success, NULL), [{Dimension}], PRE_AGG) / countOver(transactioncode, [{Dimensiom}], PRE_AGG)
``````

I think there’s also something wrong in your count function. If your condition is true (transactioncode = 14 OR transactioncode = 17), then you end up with count(‘Success’). Are you trying to count the number of occurrences of the string value ‘Success’? In what field? Is ‘Success’ a field or is it a string value?

1 Like

It’s a field.
It counts the ‘success’ occurrences and divides them by total count to get the success rate.

@Ciku

If Success is a field, it shouldn’t be enclosed in single quotes. Can you try this?

``````countOver(ifelse(transactioncode = 14 OR transactioncode = 17, Success, NULL), [{Dimension}], PRE_AGG) / countOver(transactioncode, [{Dimension}], PRE_AGG)
``````

Without seeing your dataset, I can’t tell what partition to use in the countOver. You have to replace Dimension in my calculated field by the appropriate field(s).

1 Like