Spotfire - count a status change

Question

And is it possible in Spotfire to use a calculated field to count a status change every time it changes from 0 to 1? e.g. if (previous row = 0 and current line =1, sum, else empty)

Answer 1

This is actually quite easy to do.

First step you need to get the value of the previous row. To do this, you need to use an aggregation and the OVER function. For instance :

First([col2]) over (Previous([col1]))

Second step, you determine the previous value is 1 and the current is 0.

Third step, you need to perform a cumulative sum. You can do all in one calculated column, but for more clarity I'll split it:

So your first intermediate column would be (let's name it TEMP):

if(([UTE-CALC]]=0) and (First([UTE-CALC]]) over (Previous([RowID]))=1),1,0)

And your final calculated column would perform the cumulative sum:

If([UTE-CALC]=0,Sum([TEMP]) OVER (AllPrevious([RowID])))

Here you go: