Undesirable result replace (stats) with (eventstats)

[phamxuantung]

I have a rather complicated query that go like this:

index=* source=* earliest=-4mon@mon latest=@mon RESPONSE_CODE="0" 
| bin _time span=1mon 
| stats count AS MonthTotal1 SUM(AMOUNT) AS MonthTotal BY MERCHANT_CODE, SUBMERCHANT_CODE, _time
| eval lastMonthStart = relative_time(now(),"-mon@mon")
| stats sum(eval(if(_time>=lastMonthStart,MonthTotal,0))) AS 1M_Total sum(eval(if(_time>=lastMonthStart,0, MonthTotal))) AS 3M_Total values(eval(if(_time>=lastMonthStart,MonthTotal1,null()))) AS Transaction sum(eval(if(_time<lastMonthStart,MonthTotal1,null()))) AS THREE_MONTHS BY SUBMERCHANT_CODE, MERCHANT_CODE
| eval 3M_Total_avg = round(3M_Total/3,2)
| eval RATE_Total = round((1M_Total/3M_Total_avg)*100,2)
| search RATE_Total>=200 OR RATE_Total=0
| join MERCHANT_CODE
[search index = *
| dedup MERCHANT_CODE
| table MERCHANT_CODE, BANK]
| table MERCHANT_CODE SUBMERCHANT_CODE, BANK, 1M_Total, RATE_Total

It seem complicated but the gist is I have to compare the lastest month total value of transaction to the average of 3 months before it for each sub-merchant, if the rate is >200%, show it in a table. The typical event go like this (I'll omit some unnecessary parts):

2021-10-25 13:52:33 TRANSACTION_ID="144479283"AMOUNT="10000", MERCHANT_TRANSACTION_CODE="17797161285", RESPONSE_CODE="0",MERCHANT_CODE="MOMOCE", SUBMERCHANT_CODE="22312"

Something to note:

- Each MERCHANT can have several SUBMERCHANT, or don't have one at all, so the field SUBMERCHANT is not always exist in events.

- Each MERCHANT have a BANK associate to it, but in another table.

 I have a query just for SUBMERCHANT as a baseline to compare results, but somehow the query above, and even if I use (eventstats) instead of (stats), all show all different results than the baseline. 

Does anyone have anyideal to untangle this mess, I'll really appreciate!

[PickleRick]

Not digging too deeply into the search - wouldn't timechart with timewrap be sufficient here? Maybe with an added foreach to sum three previous months?

[phamxuantung]

I'm not very familiar with the use of timewarp or foreach, I only use the method I know of. Will timewarp and foreach solve my problems and how do I incorporate it in my search?

[PickleRick]

Timewrap, as the name suggests wraps the results of timechart around a specified timerange. So if you have some results for - let's say - half a year and you timewrap it aound 1 month, you'll get 6 values in each result row - one from first month, another from second month and so on.

Foreach lets you iterate over fields (in case of timechart|timewrap|foreach would let you to iteratw over your warp-span spaced results.