Question
Get the second last status for each case
In my activities table, I have activities like set status and change status. I want to calculate what is second last status value for each case, the status values are given in the changed_to column next to the activity column
+2Hi @Abhitndn
you can use INDEX_ACTIVITY_ORDER_REVERSE, which indexes the activity table in a reverse order. So you want to filter where the result of this function returns 2.
Since you are only interested in status activities, you have to first map all others to NULL, such that the index function skips them. So the overall filter statement would look something like this:
FILTER INDEX_ACTIVITY_ORDER_REVERSE(CASE WHEN "Activities"."Activity" IN ('Set Status', 'Change Status') THEN "Activities"."Activity" ELSE NULL END) = 2;
Best
DavidLogin to the community
No account yet? Create an account
Login with SSO
Celonaut Login Celonis ID LoginEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.