Dashboards & Visualizations

How to group events by time range and show the count in a Pie Chart?

Builder

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?

0 Karma
1 Solution

Champion

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

View solution in original post

0 Karma

Champion

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

View solution in original post

0 Karma

Builder

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.

0 Karma

Champion

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?

0 Karma

Champion

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?

0 Karma

Champion

hi @ips_mandar
Any updates?

0 Karma

Builder

Thanks I resolved issue with said idea

0 Karma