Determining Date differences using OVER with IDs and stages

[John McTavish 2]

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.

 

Thanks!

[John McTavish 2]

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.

[Richard Pobi]

Chech the below link, it might assist you to archieve what you looking for.

https://community.spotfire.com/questions/calculate-date-difference-same-col...

[John McTavish 2]

Hey Rich, thanks for responding. I was using that discussion for reference but none of the solutions there work. Unfortunately the complexity seems to be because I'm trying to calculate the difference between Stage 2, Set Date and Stage 1 Unset Date.

 

I've tried multiple variations on Datediff mins and overs and can't seem to pull a proper result.

[Richard Pobi]

Can you upload a sample dxp or add image of what you trying to archieve

[John McTavish 2]

I've made some progress, in that at least I'm returning correct data for some of the stages. But the calculated results are seemingly populated at random with some stages almost full and some only 1 or 2 or no values.

Here's what I've come up with so I'm on the right track.

DateDiff([setDate],Max([unsetDate]) OVER (Intersect([id],previous([unsetDate]))))

[John McTavish 2]

It works periodically in my randomized data set, and when I export the visualization to a data set the expression I pasted below works fine, presumably because the data is properly organized by ID.

So if I take that data, randomize it, and bring it back in, the incorrect results are back.

Please see sample DXP for reference.

[John McTavish 2]

You bet, thanks Rich. See comment with attachment.