# How to convert derivative of doc\_count into percentage change?

**URL:** <https://discuss.elastic.co/t/how-to-convert-derivative-of-doc-count-into-percentage-change/231242>\
**Category:** Kibana\
**Created:** [May 6, 2020, 2:46am UTC](https://discuss.elastic.co/t/how-to-convert-derivative-of-doc-count-into-percentage-change/231242 "2020-05-06T02:46:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![vdalvi](https://avatars.discourse-cdn.com/v4/letter/v/54ee81/32.png) [@vdalvi](https://discuss.elastic.co/u/vdalvi)\
**Post date:** [May 6, 2020, 2:46am UTC](https://discuss.elastic.co/t/how-to-convert-derivative-of-doc-count-into-percentage-change/231242/1 "2020-05-06T02:46:01Z")

</div>

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

```auto
{
  "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:

```auto
{
                "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

---

<div class="post-metadata">

**Author:** ![rashmi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rashmi/32/16391_2.png) [@rashmi](https://discuss.elastic.co/u/rashmi)\
**Post date:** [May 6, 2020, 5:47pm UTC](https://discuss.elastic.co/t/how-to-convert-derivative-of-doc-count-into-percentage-change/231242/2 "2020-05-06T17:47:36Z")

</div>

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:

- [https://www.elastic.co/blog/using-painless-kibana-scripted-fields](https://www.elastic.co/blog/using-painless-kibana-scripted-fields)
- [https://www.elastic.co/guide/en/kibana/current/scripted-fields.html](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html)

Thanks  
Rashmi

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 3, 2020, 5:47pm UTC](https://discuss.elastic.co/t/how-to-convert-derivative-of-doc-count-into-percentage-change/231242/3 "2020-06-03T17:47:39Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.