Jump to content

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


Heidi Lehr 2

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

×
×
  • Create New...