Calculate last 3 months count average and compare the result with last month count

[avni26]

I want to calculate last 3months count and take its average and need to compare with last month total count.
For example:
last Month, August = 350
July = 320
June = 347
May = 300
Need to apply condition in my base query that
Last month count < avg of last 3 month
350 < ((320+347+300/3))
So for that , need to calculate last 3 months count and last month count in same query.
Please suggest.

[somesoni2 · Accepted solution]

Give this a try

your base search selecting data for last 4 months (current month+3 months before that)
| timechart span=1mon count
| eval Last3Months=if(_time<relative_time(now()-"1mon@mon"),count,0)
| eval LastMonth=if(_time=relative_time(now()-"1mon@mon"),count,0)
| stats avg(Last3Months) as Last3MonthAvg values(LastMonth) as LastMonth
| eval result=if(LastMonth<Last3MonthAvg ,"Some Message","Something else")

[avni26]

@somesoni2
with small modification its worked. Thank you for your help 🙂

[jpolvino]

One way to accomplish this is to use appendcols.

earliest=-4mon@mon latest=-1mon@mon-1s (your search here)
| timechart span=1mon count as VolLast3
| stats avg(VolLast3) as AvgLast3
| appendcols [search earliest=-1mon@mon latest=@mon-1s (your search here)
| stats count as VolLastMonth]
| eval breach=if(VolLastMonth>AvgLast3,"Breached","OK")

The main search before appendcols gets each month's count from May, June, July and averages those into AvgLast3.

Then the appendcols uses a search to look at just the month of August, storing that count in VolLastMonth. Finally, a comparison is made.