Hi Experts, I have a one month data inputlookup file i.e, sample.csv which contains two fields test and _time. I want to compare weekly data.
sample.csv
test _time
101 202/04/02 14:02:18
102 202/04/01 20:21:50
101 202/04/05 02:09:12
101 202/03/31 08:11:29
Expected output:
test count week
101 2 thisweek
102 1 lastweek
103 1 prior week
101 1 prior week
like this.. please help on this and thanks in advance.
sample:
| makeresults
| eval _raw="test time
101 2020/04/02 14:02:18
102 2020/04/01 20:21:50
101 2020/04/05 02:09:12
101 2020/03/31 08:11:29"
| multikv forceheader=1
| eval _time=strptime(time,"%Y/%m/%d %T")
| eval today = now()
| eval daysago=mvindex(split(tostring(today - _time,"duration"),"+"),0)
| eval week=case(daysago<=7,"thisweek",daysago > 7 AND daysago <= 14 ,"lastweek" , true(), "prior week")
| stats count by test week
Recommend:
| inputlookup sample.csv
| eval _time=strptime(time,"%Y/%m/%d %T")
| eval today = now()
| eval daysago=mvindex(split(tostring(today - _time,"duration"),"+"),0)
| eval week=case(daysago<=7,"thisweek",daysago > 7 AND daysago <= 14 ,"lastweek" , true(), "prior week")
| stats count by test week
How about this?
Check if this query works for you. Note that below query sets this_week_start
to 2020/04/06 00:00:00 (starting this Monday) and last_week_start
to 2020/03/30 00:00:00 (starting previous week Monday) when I posted this answer.
| inputlookup sample.csv
| eval _time=strptime(_time, "%Y/%m/%d %H:%M:%S"), this_week_start=relative_time(now(), "@w1"), last_week_start=relative_time(now(), "-w@w1"), week = case(_time > this_week_start, "thisweek", _time > last_week_start, "lastweek", 1==1, "prior week")
| stats count by test, week
@manjunathmeti , Thanks for the quick response, but this not working. all results showing as prior week only that to total count for 30days.