I am trying to get the datediff for the next row based on multiple intersects.

[Heidi Lehr 2]

I am using Spotfire 11.x.

Below is a table of data that I am trying to get the days between the next delivery date so I can get a qty/day for analysis. This is all for one well and I am not sure how to do an over statement that will use Wellname & Chemical. I wasn't sure how to use a multiple intersect in an over expression to get the datediff from the next row, in time sequence. Any suggestions, or can this even be done I even tried a ranking based on Wellname, Chemical, Date.

 

 

 

Date

Qty

$/gal

Chemical

Chemical Type

 

 

11/30/2021

55

1.00

DMO-2063

Demulsifier

 

 

1/11/2022

55

1.00

DMO-2063

Demulsifier

 

 

9/21/2021

55

2.00

SCV-3004

H2S Scavenger

 

 

11/2/2021

55

2.00

SCV-3004

H2S Scavenger

 

 

11/30/2021

55

2.00

SCV-3004

H2S Scavenger

 

 

1/28/2022

55

4.00

PIO-1073

Paraffin Inhibitor

 

 

3/1/2022

55

4.00

PIO-1073

Paraffin Inhibitor

 

 

1/11/2022

55

3.00

SCW-4097

S/C Inhibitor

 

 

3/1/2022

55

3.00

SCW-4097

S/C Inhibitor

[Fredrik Rosell 3]

Hello,

 

 

 

To ensure that any responses are relevant for the particular TIBCO product that you are using, please add a note about what that product is (or edit the question, and select a product in the Product field).

 

 

 

Best Regards

 

Fredrik

[Fabian Duerr]

Yes, you can use ranking to create a column that will help you:

Rank([Date],[Wellname],[Chemical])Then create another calculated column to find the previous date:

First([Date]) OVER (Intersect([Wellname],[Chemical],Previous([your.rank.column])))Finally, use the DateDiff() function to get the number of days.

[Heidi Lehr 2]

Thx! That works to get the previous date.

 

I guess I should have been a little better in describing what i need. I need to get the next date so I can calculate usage between delivery dates.

 

Delivery Date

Rank

Chemical

qty

Prev Del Date

Next Date

Diff

GPD

11/30/2021

1

DMO-2063

55

 

1/11/2022

42

1.31

1/11/2022

2

DMO-2063

55

11/30/2021

 

 

 

1/28/2022

1

PIO-1073

55

 

3/1/2022

32

[Fabian Duerr]

The date difference will not change anyways. ;) Just the data row will change, if you work with Next() instead of Previous()

 

Not sure if you found the solution yourself, so here are the three columns you need

 

Rank([Date],[Chemical])

 

First([Date]) OVER (Intersect([Chemical],Next([your.rank.column])))

 

DateDiff('day',[Date],[your.next.date.column])

[Heidi Lehr 2]

Oh heck...I was trying to put next in the wrong place so I couldn't get it to work. Thx!