How to convert derivative of doc_count into percentage change?

Hi,

Below is my query. I have a aggregation "count_deriv" and want to convert the delta value generated into a percentage value (i.e divide it by the doc_count from previous interval)? I would like to calculate the percentage change compared to previous interval...

Query Below

{
  "query": {
    "range": {
      "timestamp": {
        "gte": "now-1h/h"
      }
    }
  },
  "size": 0,
  "aggs": {
    "get_cmtsId": {
      "terms": {
        "field": "cmtsId",
        "size": 1
      },
    "aggs": {
      "every30min": {
        "date_histogram": {
          "field": "timestamp",
          "fixed_interval": "30m"
        },
        "aggs": {
          "count_deriv": {
            "derivative": {
              "buckets_path": "_count"
              }
            }
          }
        }
      }
    }
  }
}

Below is certain portion of the result:

{
                "key_as_string" : "2020-05-06T02:00:00.000Z",
                "key" : 1588730400000,
                "doc_count" : 603972,
                "count_deriv" : {
                  "value" : 0.0
                }
              },
              {
                "key_as_string" : "2020-05-06T02:30:00.000Z",
                "key" : 1588732200000,
                "doc_count" : 100662,
                "count_deriv" : {
                  "value" : -503310.0
                }

My goal is to basically do (New Value - Old Value)/Old Value = (-503310)/603972 * 100 = -83.333

Please help

You could either reindex your data to already have this field or use scripted fields to achieve the same (but it will be way slower)

to do it with scripted fields:

  • go to management, index patterns, select your index pattern, select scripted fields, add a new one
  • in the script enter something for example:

return 100 * doc['field1name'].value / doc['field2name'].value

  • give it a name and save it, now you can use this field as any other

some docs:

Thanks
Rashmi