8 Replies Latest reply: May 4, 2017 10:06 AM by Antonio Mancini RSS

    Need to count number of consecutive weeks with the same status

    Nikita Naresh

      Hello,

       

      I have a table like this:

      DateTarget IDStatus
      May-01A1Red
      Apr-24A1Red
      Apr-17A1

      Red

      Apr-10A1Red
      Apr-3A1Green
      Mar-27A1Red

      I need to flag all targets that have been red for 3 or more weeks and also count the number of consecutive weeks that it has stayed red. So, basically I need to count the number of rows where Status=Red starting from the top and stop counting once I reach the fifth row where the status has changed. Any prior incidents where the status was red should be ignored.

       

      Every week, I'll get a new row of data with an updated status so the date/row numbers will need to be dynamic.