For a throughput time prediction I have to get the next-to-last activity (the last activity is a running one). How can I do that? I thought about doing it in the data transformation with python but Id rather use Celonis directly.
Page 1 / 1
Hi,
you can use the following query to get the second-to-last activity for each case:
CALC_REWORK returns the total number of activities for each case, and PROCESS_ORDER indexes each activity with its position within the case.
As you want to do throughput time calculations, you may want to return the timestamp related to the second-tp-last activity:
Best
David
you can use the following query to get the second-to-last activity for each case:
PU_FIRST("Cases", "Activities"."Activity", CALC_REWORK ()-1 = PROCESS_ORDER ( "Activities"."Activity" ) )CALC_REWORK returns the total number of activities for each case, and PROCESS_ORDER indexes each activity with its position within the case.
As you want to do throughput time calculations, you may want to return the timestamp related to the second-tp-last activity:
PU_FIRST("Cases", "Activities"."Timestamp", CALC_REWORK ()-1 = PROCESS_ORDER ( "Activities"."Activity" ) )Best
David
Hello David, thank you so much! It works perfectly! Kr, Mara
Hello David, regarding to this I have another question: I have created a saved formula for calculating the remaining time out of the historical data.
Id like to calculate These values per case in an OLAP table. My Dimension is the case id and my KPI is:
Now my remaining time is calculated per case, not globally over all cases. How can I solve that?
Kr, Mara
AVG(SECONDS_BETWEEN(PU_LAST("Eventlog_cases_csv", "Eventlog_activities_csv"."TIMESTAMP", "Eventlog_activities_csv"."ACTIVITY_DET" = {p1}), PU_LAST("Eventlog_cases_csv", "Eventlog_activities_csv"."TIMESTAMP")))
Id like to calculate These values per case in an OLAP table. My Dimension is the case id and my KPI is:
KPI("Remaining_time", PU_FIRST("Eventlog_cases_csv", "Eventlog_activities_csv"."ACTIVITY_DET", CALC_REWORK ()-1 = PROCESS_ORDER ("Eventlog_activities_csv"."ACTIVITY_DET") ))Now my remaining time is calculated per case, not globally over all cases. How can I solve that?
Kr, Mara
Hi Mara,
not sure what you are trying to achieve, do you want to calculate the Avg overall and use the result in an olap table which has the Case ID as a Dimension?
In that case you can for example wrap the AVG into a GLOBAL:
GLOBAL ( AVG ( SECONDS_BETWEEN ( ) ) )
GLOBAL makes the aggregation function inside to ignore the dimension that you have.
Best
David
not sure what you are trying to achieve, do you want to calculate the Avg overall and use the result in an olap table which has the Case ID as a Dimension?
In that case you can for example wrap the AVG into a GLOBAL:
GLOBAL ( AVG ( SECONDS_BETWEEN ( ) ) )
GLOBAL makes the aggregation function inside to ignore the dimension that you have.
Best
David
Hello David,
I want to calculate the predicted throughput time per case. Therefore, I take the next-to-last activity per case and calculate how Long the historic processes had needed from this Point on. For a first try I just want to Show the estimated remaining time. After that I would add the time the case has already run.
I have added the global to my saved formula but in my OLAP table the values are still Zero.
I have also tested it by adding a component number and set a fix value for p1. This is working.
Kr, Mara
I want to calculate the predicted throughput time per case. Therefore, I take the next-to-last activity per case and calculate how Long the historic processes had needed from this Point on. For a first try I just want to Show the estimated remaining time. After that I would add the time the case has already run.
I have added the global to my saved formula but in my OLAP table the values are still Zero.
I have also tested it by adding a component number and set a fix value for p1. This is working.
Kr, Mara
Hi Mara,
so you want to subtract the AVG grouped by case from the global AVG, right?
So in that case remove the GLOBAL from your Saved formula and use
KPI() - GLOBAL(KPI())
in your OLAP table. the first KPI() call is grouped by the dimension (in your case, the case ID), and the second KPI() call inside the GLOBAL is not grouped by that, and returns the global average instead.
Best
David
so you want to subtract the AVG grouped by case from the global AVG, right?
So in that case remove the GLOBAL from your Saved formula and use
KPI() - GLOBAL(KPI())
in your OLAP table. the first KPI() call is grouped by the dimension (in your case, the case ID), and the second KPI() call inside the GLOBAL is not grouped by that, and returns the global average instead.
Best
David
Hello David,
I tried to Show my Problem with a graphic. I know how to calculate my formulas, thats no big deal. But the strange Thing is that I always get 0 for the estimated remaining time.
TP-estimation1240591 23.6 KB
Kr, Mara
I tried to Show my Problem with a graphic. I know how to calculate my formulas, thats no big deal. But the strange Thing is that I always get 0 for the estimated remaining time.
TP-estimation1240591 23.6 KB
Kr, Mara
I have tested also with putting the global into the saved formula again and removing it from the OLAP table.
I have understood the problem: It does not send the next-to-last activity as a string to the formula but sends the formula itself. So it takes the global next-to-last activity. I dont know whether thats a bug. I would say so.
Is there any possibility to solve it with a workaround and send the string itself?
I have understood the problem: It does not send the next-to-last activity as a string to the formula but sends the formula itself. So it takes the global next-to-last activity. I dont know whether thats a bug. I would say so.
Is there any possibility to solve it with a workaround and send the string itself?
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.