Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Switch in modified date

I have this list:

This table is about testing. When a test is one, it is good. When it is two, it is bad. Every month there will come a new value for some rows (not every row). It depends on when it is tested. I want to count the total of good tested rows. Therefor it needs to find the last number in de row. If it is a one, then the whole row gets the value one in my calculation. If it has a one in july and later in august a two, then it needs to get a two. But if in september it will be good again it needs to switch to the one again and ignore the two. And so on for every month. 

 

I now have:

Row 5 : a list of total tested distinct items in all the months

Row 6 + 7: filtered where the outcome is 1 or 3. This are good outcomes

Row 8: minus where the outcome is 2.

So I get in to trouble when there will be a 1 after the value of 2. It will count it double. I don't want that. The 1 AFTER a 2 or a 3 AFTER a 2 needs to be superior.

 

Can somebody help me?

 

 

1 Reply

  • I think you have bigger problems.  Your data structure is not sustainable. You will want to unpivot your data and bring it into a form like 

     

    Item | Date | Result

     

    Then it is trivial to use TOPN(1) or MAX() to find the latest result for each item.