Jump to content
We've recently updated our Privacy Statement, available here. ×

Count consecutive occurrences of '1' in data

Jishnu Sahney

Recommended Posts

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!


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...