Nesting of aggregrate functions is not allowed error

Question & Answer
, , , ,
1

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?

2

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.

3

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

4

@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
5

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

6

@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
7

Hi @Ciku,
It’s been awhile since we last heard from you; following up to see if you have any additional questions or if you were able to find a work around?

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

Thank you!

8

Hi @Ciku,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for any relevant information that may be needed.

Thank you!