+7Hello Everyone,
I have a scenario where I need to calculate the automation rate of a process. However, there is a specific condition: I need to exclude all activities that occur between the Approval Task Start and Approval Task End. These approval segments may appear multiple (N) times throughout the entire process.
Please let me know if you have encountered a similar situation before or if you have any recommended approach for handling this type of exclusion logic.
+8Hi kailash,
indeed a tricky requirement the way you described it. I do not have a good solution but thought a few minutes about your problem maybe the direction helps you. As apparently there is no solutions so far by someone else. So either way I want to subscribe to see if you come up with a proper solution at the end.
I’d consider the PQL functions under “Process” https://docs.celonis.com/en/process.html.
Especially I was thinking around SOURCE-TARGET.
This unfortunately requires a specific offset to the activity Approval Task Start /End but maybe you can create messy case when Source (1 step) = ‘Approval Task Start” OR SOURCE (2 step) = ‘Approval Task Start” …. THEN NULL ELSE AUTOMATION Status.
Hi Kailash,
sorry for only getting back to you now, I hope your request ist still relevant. It’s tricky indeed, but I’ve got a similar case and solved it that way:
First I generated a calculated Attribute in my Event-Log so i turn my flag-events into numbers, let’s call it “FLAG_INDICATOR” for now:
CASE
WHEN EVENT_LOG.EVENT = ‘Approval Task Start’ THEN 1
WHEN EVENT_LOG.EVENT = ‘Approval Task End’ THEN -1
ELSE 0
END
The second step is another calculated attribute in the Event-Log which uses RUNNING_SUM to flag all between, let’s call the calculated attribute “FLAG”:
RUNNING_SUM(EVENT_LOG.FLAG_INDICATOR, PARTITION BY (EVENT_LOG.CASE_ID))
Now you’ve got a Flag beside every unwanted Event.
Now you can use this in your further PQL for calculating the automation rate with a CASE WHEN-Statement which should look like this:
CASE WHEN EVENT_LOG.FLAG = 1 THEN NULL ELSE EVENT_LOG.*THE COLUMN YOU USE FOR THE CALC* END
After all, if you don’t want to create a calculated attribute (I only can strongly recommend using calculated attributes), you can do all in one step, but It’s more understandable whats happening when it is splitted in two attributes - the PQL for all in one would look like this:
CASE WHEN RUNNING_SUM(
CASE
WHEN EVENT_LOG.EVENT = ‘Approval Task Start’ THEN 1
WHEN EVENT_LOG.EVENT = ‘Approval Task End’ THEN -1
ELSE 0
END
, PARTITION BY (EVENT_LOG.CASE_ID)
) = 1 THEN NULL ELSE EVENT_LOG.*THE COLUMN YOU USE FOR THE CALC* END
I hope I could help you.
Best regards,
Fabian
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.