How do you compare a previous average with the current actual count?

[rohitmaheshwari]

Hello,

I am trying to write an SPL to do the below but hitting a road block. Can someone please help!!

Date                randomNumber
8/1/18              127
8/1/18              155
8/1/18              115
8/1/18              115
8/2/18              150
8/2/18              143
8/2/18              151
9/1/18              102
9/1/18              117
9/1/18              198
9/2/18              122
9/2/18              111
9/2/18              139
9/2/18              184
10/1/18             148
10/1/18             121
10/1/18             108
10/2/18             160
10/2/18             166

Scenario:
Considering that the current month is October (10). I am trying to compare the current count of random numbers that I have received on the 10/1 and 10/2 to the average of the counts that I have received on the 1st and 2nd of September(09) and August(08).
The results that I would get would be:

day1Actual = 3 for October 1st 
day2Actual = 2 for October 2nd
day1Average = 3.5 for August and September average (7 divided by 2)
day2Average = 3.5 for August and September average (7 divided by 2)

[whrg]

Hi! Here is a search for you to calculate day1Average, day2Average, day3Average etc.:

... | rex field=Date "(?<Month>\d+)/(?<Day>\d+)/(?<Year>\d+)"
| stats count as Count by Year,Month,Day | sort Year,Month,Day
| eventstats last(Month) as Current_Month last(Year) as Current_Year | where Month!=Current_Month OR Year!=Current_Year
| stats avg(Count) as DayAverage values(Month) as Months by Day

This will give you:

Day    DayAverage    Months
1      3.5           8,9
2      3.5           8,9

To get day1Actual and day2Actual you would need to replace the where clause with:

| where Month=Current_Month AND Year=Current_Year