How to see what is the next/previous node and how to change the node queue (Over functions)

[Bernard Clark]

I have a series of dates stored in the [Date] column for specific miletsones that are stored in the [Variable] column and other flags in [DG] and [Project Name] columns.

I need to determine the amount of time that each project spent at each milestone ([Variable]).

the milestones (in order) are

 

DG1

DG2

DG3

FID

1st Production

DG5

 

They are entered in the database in sucessive rows in that order, and have the custom sort order for [Variable] specified as that as well. There are ~70 other data entry classifications in the [Variable] column as well.

I am trying to use:

max([Date]) over (Intersect([Well Name],[DG],Previous([Variable])))

Which appears to work, until you notice that it is taking the previous node for DG5asDG4instead of1st Production, and that FID and 1st Production are left blank.

So, how do I see where Spotfire is assigning the node order, and how do I change it to the correct/desired order Am I stuck with it just being alphabetical forever

[Fabian Duerr]

As a workaround create a calculated column with the desired order and use this helper column for your over function. You can hide it in your original table.

 

case

when [Variable] = "DG1" then "order_1"

when [Variable] = "DG2" then "order_2"

... and so on...

end 

 

 

 

Which appears to work, until you notice that it is taking the previous node for DG5 as DG4 instead of 1st Production, and that FID and 1st Production are left blank. 

 

It seems that Spotfire tries to identify the nodes by the names. So DG1,2,3,4,5 are one node. And I guess that ABC1,2,3,4 would also be a node, but 1st Production and FID differ from nomenclature.

 

Let's see if any one comes up with a complete answer