<row>
<panel depends="$stayhidden$">
<title>Colour based on previous</title>
<html>
<style>
#tableCellColourWithoutJS table tbody td div.multivalue-subcell[data-mv-index="1"]{
display: none;
}
</style>
</html>
</panel>
<panel>
<table id="tableCellColourWithoutJS">
<title>Colour Cell by Previous</title>
<search>
<query>| makeresults | eval _raw="Messages Nov_20 Dec_20 Jan_21 Feb_21
Messge 0 0 1 0 0
Messge 1 1 3 1 1
Messge 2 11 0 0 0
Messge 3 1 0 0 0
Messge 4 9 5 0 0
Messge 5 1 1 0 0
Messge 6 1 1 0 0
Messge 7 0 1 0 0"
| multikv forceheader=1
| fields - _raw _time linecount
| fields - _mkv*
| transpose 0 header_field=Messages column_name=Month
| eval Month=strptime(Month . "_01", "%b_%y_%d")
| sort - Month
| autoregress Month as next p=1
| eval months=mvappend(Month, next)
| fields - next
| mvexpand months
| stats list(*) as * by months
| where months != "months"
| foreach *
[ eval sign=(tonumber(mvindex('<<FIELD>>',0)) - tonumber(mvindex('<<FIELD>>', 1)))
| eval sign = sign / abs(sign)
| fillnull value=0 sign
| eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW"))
| eval <<FIELD>>=mvappend(mvindex('<<FIELD>>', 0),sign)
| fields - sign ]
| fields - months
| eval Month=mvindex(Month,0)
| sort Month
| eval Month=strftime(Month, "%b %y")
| transpose 0 header_field=Month column_name=Messages</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">none</option>
<option name="percentagesRow">false</option>
<option name="refresh.display">progressbar</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<format type="color">
<colorPalette type="expression">case (match(value,"RED"), "#ff0000",match(value,"YELLOW"), "#ffff00",match(value,"GREEN"),"#00ff00",true(),"#ffffff")</colorPalette>
</format>
</table>
</panel>
</row>
The hidden panel sets up the style to hide the second multi-value on each row (which contains the required colour).
The first part of the query sets up your example data - I assumed you meant Jan and Feb 21 not 20
By parsing the month to epoch time format it can then be used to sort
Getting the next month allows it to be "joined" with the previous month with the stats command so each month has the previous months value for each message
For each field, evaluate if the value for the message is greater or less or equal to previous month's value and set colour accordingly
Sort by month again, then format to shown date format
Finally, convert back to desired table layout
Set colour palette according to value of (hidden) field
| index=sample check=ERROR
| stats first(_time) as _time by SampleCode, Message
| rex mode=sed field=Message
"s/failed: //g
s/Order.ScenarioId/Order-ScenarioId/
s/([0-9])[\.|\_]([0-9])/\1\2/
s/[0-9]+//
s/(PREOK)?(ID)?([A-Z]+_[A-Z]+_?[A-Z]+)?(\b([A-Z]{1,}\b))?/\1\2\3/g
s/ / /"
| rex field=ComplianceAcknowledgementMessage "^(?<ResponseMsg>[ a-zA-Z \-,]+)"
| bin span=mon _time
| stats count by _time ResponseMsg
| sort ResponseMsg _time
| autoregress count as previousCount p=1
| autoregress ResponseMsg as previousMsg p=1
| eval sign=if(ResponseMsg=previousMsg,count-previousCount,null)
| eval sign = sign/abs(sign)
| fillnull value=0
| eval sign=if(sign < 0, "RED", if(sign > 0, "GREEN", "YELLOW"))
| eval count=mvappend(count,sign)
| fields - sign previousCount previousMsg
| eval _time = strftime(_time,"%b %y")
| xyseries ResponseMsg _time count
you can see this the output i am getting and the its not coming in correct order Jan21 and Feb 21 should be coming at last
Tried with the query you gave but its not working as expected, When i check for last 30 days of data we are seeing most empty for Feb month and colors need to be applied based on previous months data. But we are seeing the yellow colors Bg-color on March month.
This the Feb and March data
