calculate the time difference for same column in S

问题:

I am a beginner for Spotfire. I have a problem about the difference calculation for the some column value. A sample table could be like this:

id  timestamp              state
 1  7/1/2016 12:00:01 AM    1
 2  7/1/2016 12:00:03 AM    0
 3  7/1/2016 12:00:04 AM    1
 4  7/1/2016 12:00:06 AM    0
 5  7/1/2016 12:00:09 AM    1
 6  7/1/2016 12:00:10 AM    0
 7  7/1/2016 12:00:12 AM    1

I want to calculate the time difference for the timestamp when the state is 1, the final table I want to have is:

id  timestamp              state  time_diffence
 3  7/1/2016 12:00:04 AM    1     3
 5  7/1/2016 12:00:09 AM    1     5
 7  7/1/2016 12:00:12 AM    1     3

it seems that I should identify an expression for the calculation, but I have not idea for the calculation just for one parameter :(. somebody could help me ?

still one more small question: what if the timestamp column value is just number value, how can i calculate the difference, is there any related function like DateDiff() here? for example:

id  times state
 1  12    1
 2   7    0
 3  10    1
 4  11    0
 5   6    1
 6   9    0
 7   7    1

the result could be :

id  times state  diffence
 3  10    1      -2
 5   6    1      -4
 7   7    1       1

after running the code: i have the error as below:

for the row if it has the same time stamp as the last previous row, the difference will keep same as before, but actually the difference for the rows which have same time stamp would be as 0

thanks for your help :)

回答1:

Assuming your data is sorted in ascending order by [timestamp] before you import it, you can partition using the Previous function with Over where the [state]=1.

Insert a calculated column with this expression:

If([state]=1,DateDiff("ss",Min([timestamp]) OVER (Previous([timestamp])),[timestamp]))

You will see it populated in your table like the below:

Then if you ONLY want to see the rows that have the difference you mentioned, on your table you can...

Right Click > Properties > Data > Limit data using expression >

And insert the expression: [time_difference] > 1

This will result in this table: