Hi,
I want to group events by time range like below-
1. 1-6am
2. 6-9 am
3. 9-3.30am
4. 3.30-6.30pm
5. 6.30-1am
and show count of event for these time range in pie chart. how can I group events by timerange?
3rd row you mean to say 9 am - 3:30 pm right?
try this, this will split all values into grps,verify the output and then sue further
NOTE - bin span of 1 h has been used to trim down counts for testing as long as the group split works thishas no impact on removal
| eval time =strftime(_time,"%I:%M%P")
| eval time1 =strptime(time,"%I:%M%P")| eval mrkr1=strptime("06:00am","%I:%M%P")
| eval mrkr2=strptime("09:00am","%I:%M%P")
| eval mrkr3=strptime("03:30pm","%I:%M%P")
| eval mrkr4=strptime("06:30pm","%I:%M%P")
| eval mrkr5=strptime("01:00am","%I:%M%P")
| eval grp=case(time1>mrkr5 AND time1 <=mrkr1,"grp1",time1>mrkr1 AND time1 <=mrkr2,"grp2",time1>mrkr2 AND time1 <=mrkr3,"grp3",time1>mrkr3 AND time1 <=mrkr4,"grp4",time1>mrk4 AND time1 <=mrkr5,"grp5")
| stats values(time) by grp
3rd row you mean to say 9 am - 3:30 pm right?
try this, this will split all values into grps,verify the output and then sue further
NOTE - bin span of 1 h has been used to trim down counts for testing as long as the group split works thishas no impact on removal
| eval time =strftime(_time,"%I:%M%P")
| eval time1 =strptime(time,"%I:%M%P")| eval mrkr1=strptime("06:00am","%I:%M%P")
| eval mrkr2=strptime("09:00am","%I:%M%P")
| eval mrkr3=strptime("03:30pm","%I:%M%P")
| eval mrkr4=strptime("06:30pm","%I:%M%P")
| eval mrkr5=strptime("01:00am","%I:%M%P")
| eval grp=case(time1>mrkr5 AND time1 <=mrkr1,"grp1",time1>mrkr1 AND time1 <=mrkr2,"grp2",time1>mrkr2 AND time1 <=mrkr3,"grp3",time1>mrkr3 AND time1 <=mrkr4,"grp4",time1>mrk4 AND time1 <=mrkr5,"grp5")
| stats values(time) by grp
Thanks for your answer ..
but it won't satisfy all condition like If my _time is 07pm then using above epoch comparison it won't give me result though it will fall under 6.30pm to 01am but epoch value 01am is less than 06.30 pm.
hmm i see what you mean..the if conditions was always going to be tricky..instead of 1 am what if we make it 12:59 PM in mrkr 5?
my main effort has been to show that you can grp by using mrkr times, the cae/ifs are a bit tricky but i think together we can fix it, what say?
hi @ips_mandar
Any updates?
Thanks I resolved issue with said idea