Archive

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

Explorer

Hello,

``````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)
``````
Tags (3)
Motivator

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
``````