Jump to content
We've recently updated our Privacy Statement, available here. ×

Determining Date differences using OVER with IDs and stages

John McTavish 2

Recommended Posts

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.



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]))))

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...