Spotfire - date diff over last

[Allison Holmes 2]

Hello,

I have a data table that looks like this:

 

 

 

Well Name

Well Ready Date

Date Updated

Rank

 

 

Alpha

7/26/2019

7/15/2019

3

 

 

Alpha

8/1/2019

7/16/2019

2

 

 

Alpha

8/2/2019

7/18/2019

1

 

 

Beta

7/18/2019

7/15/2019

2

 

 

Beta

7/30/2019

7/17/2019

1

 

 

 

I'm trying to track how these well ready dates change each time I get an update. So for Alpha, I originally show that the well would be ready on 7/26/2019, but that date has changed twice and as of today it looks like it will be ready 8/2/2019. My rank column is just a calculated descending rank of the date updated for each well name, I can change that if necessary.

I want to create two calculated columns to determine:

1) the number of days between a most recently provided "ready" date and the first "ready" dateI receivedfor each Well Name. So for the Alpha well, I need the date difference between 8/2/2019 and 7/26/2019

2)the number of days between a most recently provided "ready" date and the last time I received a"ready date" I receivedfor each Well Name. So for the Alpha well, I need the date difference between 8/2/2019 and 8/1/2019

Can anyone assist

[David Boot-Olazabal]

Hi Allison,

Regarding your first calculated column, you can use this expression: DateDiff("dd",min([Well Ready Date]),Max([Well Ready Date]) OVER ([Well Name])).

This will show your data as seen below:

Well Name Well Ready Date Date Updated Rank datediff

Alpha 7/26/2019 7/15/2019 3 15

Alpha 8/1/2019 7/16/2019 2 15

Alpha 8/2/2019 7/18/2019 1 15

Beta 7/18/2019 7/15/2019 2 12

Beta 7/30/2019 7/17/2019 1 12.

Regarding your second calculated column, you may have to create some extra columns first in which you define the latest row per well name (min([Rank)) and the previous one (min([Rank])+1). And then set up your final calculation, a bit similar to the first one, but taking into account the last row and before last row colums. This is a bit harder though, as your results will not be on the same row, so you need to find a solution to get both dates on the same row. So you might want to look to a transformation that returns your dates on the same row, so your calculation will be done accordingly.

kind regards,

David Boot-Olazabal

[Allison Holmes 2]

Update - I got this to work with an intersect/previous calculated column.