Thank you @dbb1 for the details. I think this can be done by creating 2 calculated fields. First Create a Activity Indicator Field to denote the value 0 for rows having activities “pay compensation”, “reject request” and remaining activities as 1.
Once that is done, please leverage that field to conditionally calculate the timespan. I have tried a smaller simpler dataset and was able to achieve it. Please find the snapshots below. Hope this helps!
Calculated Field Details:
// Instead of the below, you can use yours like this : if(in(activity, [“pay compensation”, “reject request” ]), 0, 1)
ifelse(activity = ‘X’, 0, 1)
ifelse(minOver({X Activity Indicator}, [{case_id}], PRE_AGG) = 0,
datediff(minOver({Activity_Date}, [{case_id}], PRE_AGG), now(), ‘DD’),
datediff(minOver({Activity_Date}, [{case_id}], PRE_AGG), maxOver({Activity_Date}, [{case_id}], PRE_AGG), ‘DD’))
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!