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.