Count consecutive occurrences of '1' in data

[Jishnu Sahney]

In my data I have calculated the % difference between two columns and flagged the rows where this % difference is lower than -10. Now I have to claculate the number of times there are 3 consecutive flags.

 

I tried the RowID() approach, however since the order of the data in the database is not as desired so that doesn't work.

 

This is how my data looks like:

Subject C1 C2 %Diff Flag

A 100 95 -5

A 80 64 -20 1

A 90 72 -20 1

A 70 59.5 -15 1

A 80 88 10

B 100 88 -12 1

B 90 72 -20 1

B 70 56 -20 1

B 100 83 -17 1

 

So for both subjects A and B the result should be that in both subjects there is 1 time that there are 3 consecutive flags.

 

Any help would be great!

Thanks

[Gaia Paolini]

Hi

 

if the order of data in the database is not right, how do you work out which rows are consecutive Is there a date or another indicator

 

Please can you elaborate on what version of Spotfire you are on, and whether you can use TERR or not

 

Gaia

[Jishnu Sahney]

Hi Gaia

 

I think I have found a way. I created a new custom rowid or rank and am now using the intersect function to gather the next flags.

 

 

 

Thanks!