Create a backlog report with ElasticSearch

[TomTom]

I have an index that stores created tasks, this index has the property of creation date and task end date.
With Elasticsearch, can I build a histogram that displays the daily backlog since the beginning of my records, but only displays a selected period?

Eg: My logs started on 2013-01-01, I need to generate a backlog report of every day, but I only want to show the user the last 7 days, but those 7 days are still filled with things that happened years ago.

Does Elasticsearch have the capabilities to do this?

Mapping:

curl -XPUT 'localhost:9200/task?pretty' -H 'Content-Type: application/json' -d '
{
    "settings": {
        "number_of_shards": 1
    },
    "mappings": {
        "start_date": {
            "type": "date",
            "index": true,
            "format": "strict_date_time_no_millis"
        },
        "end_date": {
            "type": "date",
            "index": true,
            "format": "strict_date_time_no_millis"
        },
        "description": {
            "type": "text",
            "index": true
        }
    }
}

[spinscale (Alexander Reelsen)]

Hey,

I don't understand the why behind this sentence:

I only want to show the user the last 7 days, but those 7 days are still filled with things that happened years ago.

Why are those 7 days filled with things that happened years ago? How does your query look like? What did you try, and what is not working as expected?

A fully minimal, but working example including index creation/mapping, sample documents and your query would help a lot to follow hte steps you did to understand where the problem is.

Thanks!

[TomTom]

Hi @spinscale ,
I'll explain what the scenario would look like and why I need to "walk through" the entire period to bring up old data.

Imagine the following timeline:

1. 2013-01-01 User John created two tasks and did not complete them
2. 2013-01-02 User John created one more task and finished the task created on the same day and only one task created on the previous day.
   The backlog had a total of 1 item (3 tasks created - 2 tasks completed in that period)
3. 2013-01-03 User John was fired, leaving that open task on day 2013-01-01 as open forever, affecting up the backlog of future days
4. 2022-04-06 On this day, 56 tasks were created, but only 55 were able to complete, this task that was not completed will be added to the previous backlog, generating a backlog of 2 tasks on that day.
5. 2022-04-07 The company manager decided to see how his team's backlog of tasks is, his team managed to finish the tasks always on the same day they were opened. For this, he decided to track the backlog of the last two days, however, even if the employees create and finish tasks on the same day, he should still see the backlog as 1, because in 2013 John never finished that task

| Date       | Openend | Closed | Backlog |
|------------|---------|--------|---------|
| 2013-01-01 | 2       | 0      | 2       |
| 2013-01-02 | 1       | 2      | 1       |
| 2022-04-05 | 62      | 62     | 1       |
| 2022-04-05 | 30      | 30     | 1       |
| 2022-04-06 | 56      | 55     | 2       | /* On this day, the user was unable to finish ONE task, it increased in the backlog */
| 2022-04-07 | 40      | 41     | 1       |

6. So, in case he decides to make a report for the day 2022-04-06 only, the backlog should be 2.

The reason I don't present an example query is because I don't know how to assemble this type of query in Elasticsearch.