Dear all,
I have documents in my index with two Date/Time fields: "Created" and "Closed". Now I want to know how many documents exist at a time on a timeline where the relative time, that is the one currently displayed/calculated on the timeline, is between these two values. So the number of documents that were created but not yet closed at that time (let's say "Open" for this, where "Open" is not a field in the index).
So for example these three documents:
Document 1:
Created: 2020-01-01
Closed: 2020-07-01
=> Open: Q1-2020, Q2-2020, Q3-2020
Document 2:
Created: 2020-05-01
Closed: 2021-01-01
=> Open: Q2-2020, Q3-2020, Q4-2020
Document 3:
Created: 2020-08-01
Closed: 2021-04-01
=> Open: Q3-2020, Q4-2020, Q1-2021, Q2-2021
On a timeline with an interval of 1 quarter (in reality I will need an hourly representation afterwards) I then need the following result:
Q1-2020: 1 (Document 1)
Q2-2020: 2 (Document 1 and 2)
Q3-2020: 3 (document 1, 2 and 3)
Q4-2020: 2 (document 2 and 3)
Q1-2021: 1 (document 3)
So I think (not sure) that I need a cumulative total, but counting the documents where the create timestamp is less than and the closed timestamp is greater than the time on the timeline. And this is for each time point on the timeline.
Unfortunately, we cannot use Timelion. So the calculation should be possible through the other tools in Kibana. I have tried to work with sub-buckets and other tools, but have not been successful so far.
Thanks for your help and have a nice evening 