How to compare last column with previously violated column under foreach?

[sangs8788]

Hi

I have a query which results me data in the below format,

I am trying to put out a table assigning priority based on the response(>2s is violator) for module and number of times violation occurred. 

| foreach *-2020 or *-2021
[ | eval LastViolatedMonth = if('<<FIELD>>'>2,"<<FIELD>>", LastViolatedMonth)
       , LastViolatedMonthNumber = substr(LastViolatedMonth, 0, 2)
       , ViolationCount=if(('<<FIELD>>'>2), ViolationCount+1, ViolationCount)
       , LastViolatedResponse=if('<<FIELD>>'>2,'<<FIELD>>', LastViolatedResponse)
       , Deviation=case(LastViolatedResponse>2,round(((LastViolatedResponse-2)/2)*100,1))
       , Priority = case(
                        (Deviation >= 100 AND ViolationCount >=1), "P1"
                      , ((Deviation >= 75 AND Deviation < 100) AND ViolationCount >=3), "P1"
                      , ((Deviation >= 75 AND Deviation < 100) AND (ViolationCount >= 0 AND ViolationCount < 3)), "P2" 
                      , ((Deviation >= 50 AND Deviation < 75) AND ViolationCount >= 3), "P2"
                      )]
| fields Module, LastViolatedMonth, LastViolatedResponse, ViolationCount, Deviation, Priority

Currently the Module is considered P1 violator when the violation count is >3.

I would like to add one more condition to check for the previous month response - if it was a violator or not.If previous month is not a violator but the latest/last month is a violator and the violation count >=3, I want that module to be marked as P2(not P1).

I am not sure how to check the previous column value(that is previous month value - to check if it violated then) against the last/latest month under for each statement. Could someone please help me out here.

 @bowesmana Can you help me on this. Thanks