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

Hi @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
David

1 Like

Thanks David that solves the issue