Get the next-to-last activity

Hi, 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. 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

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

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

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 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?