Ranking based on aggregated columns

Hi,
I have a table of Agent who first performed an activity A calculated using (PU_FIRST(Case,Activity_Table.Agent_Name,Activity_Name"="Activity_A) and KPI of Number of times a particular activity was repeated within 7 days using :
SUM(CASE WHEN
DATEDIFF(dd,LAG(WorkOrder.CreatedDate,ORDER BY (WorkOrder.CreatedDate), PARTITION BY (Case.CaseNumber)),WorkOrder.CreatedDate)
< 7 THEN 1 ELSE 0 END) .
I want to rank the agents based on this KPI as another column.
Can you please guide me on how to do the same ?
Thank you for the help

Page 1 / 1

@tamanna.taman not quite sure what exactly you want to achieve but maybe INDEX_ORDER helps here?

https://confluence.celonis.com/display/PQLdevelopment/INDEX_ORDER

Or maybe you can elaborate a little bit further?

Hope this helps,

Kevi

Hello Tamanna,

You can the following formula to rank the KPI outcomes

INDEX_ORDER(SUM(CASE WHEN DATEDIFF(dd,LAG(WorkOrder.CreatedDate,ORDER BY (WorkOrder.CreatedDate), PARTITION BY (Case.CaseNumber)),WorkOrder.CreatedDate) < 7 THEN 1 ELSE 0 END)

)

Works with both columns and aggregated KPIs

Best,

Gabriel

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN