I need to get the previous row value over "ID" column. I am trying to get the "Last status change date" by adding a calculated column in spotfire. Help me to add this in spotfire. I have multiple IDs, My resultant calculated column should be having previous "Status Change Date"

below is the sample data

I need to get the previous row value over "ID" column. I am trying to get the "Last status change date" by adding a calculated column in spotfire. Help me to add this in spotfire. I have multiple IDs, My resultant calculated column should be having previous "Status Change Date"

below is the sample data

• spotfire
• spotfire-analyst

2 Answers 2

I am not clear why you do not expect every row to have a Last Status Change Date.

This would give you a value for every row:

First create a [Row_ID] column as RowId(). Unfortunately Spotfire does not allow you to call a function after an OVER expression, so you need the column, unless you already have a row identifier.

Then use it to calculate your Last Status Change Date as:

Max([Status_Change_Date]) over (intersect(AllPrevious([Row_ID]),[ID]))

I have got the answer by doing this,

First I inserted rank of my status change by ID

 If([Status Change Date] is not NULL,Rank(RowId(),"asc",[ID]))```

then I inserted onw more calculated column to get the last status change date using rank

Last([Status Change Date]) OVER (Intersect([task_id],Previous([Rank of Status Change Date])))

This gave me the Last Status Change Date