Greetings!
Trying to build a search that automatically compares volume for this year against the same day of the week last year - however, I am having trouble figuring out how to get the same day of the week a year ago...
earliest=1y@(monday last year) latest=1y@(monday last year)
Try this to set the earliest and latest dynamically using subsearch (from today).
your base search terms
[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""]
| rest of search
The problem is that the day of the week changes, so 02/Dec today is Tue, last year 02/Dec/2013 was Sun... so, you'll need some coding to define wish Tuesday from last year to be used, the closest "up" or closest "down".
Now, the easier option is to use something like: earliest=-364d@d latest=-365d@d
As 364/7 = 52, you'll always get the values from exactly 52 weeks ago, at the same day of the week as Today. If Today is Tuesday and you want to check the results from yesterday (Monday), just subtract one (or add 6): earliest=-363d@d latest=-364d@d
Hopefully it'll give you some direction.
Try this to set the earliest and latest dynamically using subsearch (from today).
your base search terms
[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""]
| rest of search
This seems to work! I like it!!
Any suggestions on working dynamics into a timechart that compares today vs last year (on the same day of the week)?
Here's my attempt
[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""] | fields _time count | eval _time=_time+(364*24*60*60) | timechart count
sideview's solution will only work for Monday. Because you can't set the earliest and latest dynamically, I suggest the following approach:
earliest=-1y@w1 latest=-1y@w6
Then you need a where clause to compare Today's wday to that weeks wday:
earliest=-1y@w1 latest=-1y@w6 | where date_wday=lower(strftime(now(),"%A"))
Use the Advanced
mode in the timerange picker and set your earliest to -1y@w1
and your latest to -1y@w1+1d
It looks extremely strange but it is legal syntax and all documented http://docs.splunk.com/Documentation/Splunk/6.2.0/Search/Specifytimemodifiersinyoursearch although the docs could have more practical examples.
Basically it's saying go back a year, then snap down to 12am on the nearest monday. And the latest time then says go back a year, snap down to 12am on monday, then go 1 day forward from there. This will in many cases be equivalent to -1y@w2
but I recommend the more explicit way with +1d
as a best practice -- In general you have to worry about the snapping behavior putting your latest time before the earliest time and the +1d
removes that worry.