Skip to main content

Hello,

Im trying to set up Action Engine skills using standard deviations from KPIs to trigger the firing. However, its not the Action Engine part Im having trouble with, its the standard deviation calculation.

Essentially Im trying to calculate the standard deviation of client abcs cancellation % from all clients cancellation %.

Ive experimented with the STDEV function but because the cancellation % function already has AVG (an aggregate) it doesnt work.

Cancellation formula:

AVG(CASE WHEN AuthMgmt_Tasks.auth_outcome IN (Cancelled after work,Cancelled prior to work)

THEN 1.0 ELSE 0.0 END)

Hoping someone has experimented with this before, appreciate any help!

Hello ztaylor,

Thanks for reaching out! As you already stated, nested aggregations are not supported. Generally, when facing nested aggregations, refer to pull up functions.

In your case, we need to get rid of the aggregation in your cancellation formula. Since you are looking for the stdev of client abcs cancelation % from all clients cancellation % and cancellation% is not based on a int or float column, PU_AVG will not yield the desired result. Nonetheless, we can still count all cancellations for each client and divided by the total number of abcs. This will yield the average cancellation % and can be used within STDEV.

This would look similar to this:

STDEV(
PU_COUNT (
DOMAIN_TABLE ( AuthMgmt_Tasks.CLIENT),
AuthMgmt_Tasks.auth_outcome,
AuthMgmt_Tasks.auth_outcome IN (Cancelled after work, Cancelled prior to work)
)
/
PU_COUNT (
DOMAIN_TABLE "AuthMgmt_Tasks"."CLIENT"),
"AuthMgmt_Tasks"."auth_outcome"
)
)

I hope this formula and the explanation help solving your problem.

Reply


Terms & Conditions

ANY QUESTION?

Talk to an expert
FAQs
Contact us

MORE CELONIS

Academy arrow_outward
Documentation arrow_outward
Support Center arrow_outward
Partner Portal arrow_outward
Marketplace arrow_outward

CHECK US IN

LinkedIn
YouTube
Instagram
Facebook
Imprint
Disclaimer
Privacy Notice
Cookie Notice
Terms and Conditions
Celonis © 2024