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
Page 1 / 1
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:
Best
David
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
Thanks David that solves the issue
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.