Sorry I didn't provide more of an explanation in my previous response. First, to make the formula work for your data table, change the "Date" column in the "If" statements to "Biopsy Result" and the "Hour" argument in the Date Difference function to whatever interval you need (i.e. "Day"):
If( Row() == 1,
nm = Loc Nonmissing( :Biopsy Result << get values );
.;
,
If( Is Missing( :Biopsy Result ),
.,
Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) )
)
)
Now for the explanation. The existing formulas don't automatically index backward or forward until they find non-missing data, so I resorted to scripting to create variables that contain the information needed. If you would like to learn more about scripting, the Help->Books->Scripting Guide and Help->Scripting Index are great resources.
The ":Biopsy Result<<Get Values" statement generates a list with all the values for Biopsy Result. Loc Nonmissing is a matrix function that finds all the positions in the list that are non-missing. The variable, "nm" stores this result, which now contains the row numbers for the non-missing Biopsy Result entries. Since this only needs to be created once, it is done for the first row only. Also, the result is set to missing for the first row.
Next, if Biopsy Result is missing, the result is set to missing, otherwise, we need to reference the previous, non-missing Date for the Date Difference calculation. Contains(nm, Row()) finds the position of the current row in the "nm" matrix. Subtract 1 from this to find the position of the previous row in the "nm" matrix. This now is the index, i.e. row number, for the Date value in the row with the previous, non-missing Biopsy Result. The "Try" function ignores the error generated by the Date Difference function when there is no previous, non-missing row (i.e. the first instance of a Biopsy Result).
A couple of things to keep in mind, this assumes your data are sorted by date within ID # (as in your example). Also, if you have multiple ID #'s in your data table, you may want to add another condition so that you're not comparing two different ID's. In this case, your formula/script might look something like this:
If( Row() == 1,
nm = Loc Nonmissing( :Biopsy Result << get values );
.;
,
If( Is Missing( :Biopsy Result ),
.,
If( :ID # == :ID #[nm[Contains( nm, Row() ) - 1]],
Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) ),
.
)
)
)
I know this is a lot of info, but you did say you were "anxious to learn this"... hope this helps.
... View more