Standard Deviation in Metrics


I’m trying to set up Action Engine skills using standard deviations from KPIs to trigger the firing. However, it’s not the Action Engine part I’m having trouble with, it’s the standard deviation calculation.

Essentially I’m trying to calculate the standard deviation of client abc’s cancellation % from all clients’ cancellation %.

I’ve experimented with the STDEV function but because the cancellation % function already has AVG (an aggregate) it doesn’t 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 abc’s 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 abc’s. This will yield the average cancellation % and can be used within STDEV.

This would look similar to this:

DOMAIN_TABLE ( “AuthMgmt_Tasks”.“CLIENT”),
“AuthMgmt_Tasks”.“auth_outcome” IN (‘Cancelled after work’, ‘Cancelled prior to work’)
DOMAIN_TABLE "AuthMgmt_Tasks"."CLIENT"),

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