Determining Date differences using OVER with IDs and stages

I'm new to Spotfire and I find myself in the deep end trying to insert a calculated column for a date difference. I've got the following situation:

I have data where I have an ID, a start and a stop date for that ID, along with a stage for that ID. What I'm trying to determine is the time / date difference between the stop date of the previous stage for that ID, and the start of the next stage for that ID.

I attempted to do this using DateDiff,Previous, and Intersect functions but I believe I'm ignoring instances where there is no DateDiff because that's the first stage. Here's what I currently have and am working on:

If([StageNumber]>1, DateDiff([SetDate],Max([UnsetDate]) OVER (Intersect([ID],previous([UnsetDate])))))

I can't wrap my head around whether the ordering is correct or makes sense for the dates.



1 Comment

If I leave the statement as is, it seems clear that it's not pulling back data because there is no row ordering in ascending date.

If use allprevious for the UnsetDate then it calculates the date difference between set and unset date on the same row instead of the previous entry. - Mar 08, 2019 - 12:52pm ::
+ Add a Comment

(4) Answers