I am trying to determine the number of entries that have a field date that is before or equal to
_time. My data set is:
_time, Opened, Closed 2016-06-01 00:20:21,2016-06-01 00:20:21,2016-06-02 02:18:23 2016-06-01 00:37:16,2016-06-01 00:37:16,2016-06-02 02:25:14 2016-06-01 01:12:07,2016-06-01 01:12:07,2016-06-03 02:44:41 2016-06-01 01:17:27,2016-06-01 01:17:27,2016-06-03 03:00:08 2016-06-02 14:01:28,2016-06-02 14:01:28,2016-06-03 02:02:50 2016-06-02 14:04:06,2016-06-02 14:04:06,2016-06-03 02:23:52 2016-06-02 14:08:53,2016-06-02 14:08:53,2016-06-04 02:05:21 2016-06-03 18:46:25,2016-06-03 18:46:25,2016-06-04 02:47:58 2016-06-03 18:47:18,2016-06-03 18:47:18,2016-06-04 02:39:18
(during import _time was created based on Opened)
My goal is to get a table that will tell me for a given day, how many entries have an
Opened value equal to or before that day along with the number of entries that have a
Closed value equal to or before that day (but only for the entries that have already been counted due to their
_time,openedCount,closedCount 2016-06-01,4,0 2016-06-02,7,2 2016-06-03,9,4
I have tried as much as I can to use the
join command, but I'm not having much luck. Right now I am experimenting with the
append command to see whether I can achieve my goal. I'm opening this question in the meantime to see whether I can get any pointers...
How about this. I am assuming
... | eval e_closed=strptime(Closed, "%Y-%m-%d %H:%M:%S") | eval given_day=relative_time(now(), "@d") | eval o_count=if(_time<=given_day, 1, 0) | eval c_count=if(e_closed<=given_day, 1, 0) | timechart span=1d sum(o_count) as opened sum(eval(if(c_count=1 AND o_count=1, 1, 0))) as closed
Thanks for the answer. It's not quite what I'm looking for but it's given me something to work with. In reality the
given_day should be the the
_time value for a particular entry.
What I'm looking for is:
Given the _time of an entry, how many objects were opened on or before that time, and how many of those same objects have been closed on or before that time?
FOR EVERY ENTRY BEFORE _time INCLUDE IF [Opened] < [_time] INCLUDE IF [Closed] < [_time]
What makes it difficult is that I have to be able to search through all previous data to make my calculation, and I am struggling to figure out how to do that.