# TSVB Metric from the difference between avg value from two time intervals

**URL:** <https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570>\
**Category:** Kibana\
**Created:** [November 7, 2021, 7:59pm UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570 "2021-11-07T19:59:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![alejandrosl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alejandrosl/32/83830_2.png) [@alejandrosl](https://discuss.elastic.co/u/alejandrosl)\
**Post date:** [November 7, 2021, 7:59pm UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570/1 "2021-11-07T19:59:21Z")

</div>

Hi there !!!  
I'm currently trying to make a "metric" TSVB that show the difference between of average of same field in two diferentes time intervals...

For example, we have a numeric field called "value\_before", we get the last 48h of each document and, with date\_histogram aggs with avg nested aggs we split the data:

```auto
POST my-index/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-48h",
        "lte": "now"
      }
    }
  }, 
  "aggs": {
    "data": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "1d"
      },
      "aggs": {
        "media": {
          "avg": {
            "field": "value_before"
          }
        }
      }
    }
  }
}

```

The result are:

```auto
{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "data" : {
      "buckets" : [
        {
          "key_as_string" : "2021-11-06T00:00:00.000Z",
          "key" : 1636156800000,
          "doc_count" : 3798,
          "media" : {
            "value" : 22.371458541725485
          }
        },
        {
          "key_as_string" : "2021-11-07T00:00:00.000Z",
          "key" : 1636243200000,
          "doc_count" : 21662,
          "media" : {
            "value" : 26.57745969695533
          }
        }
      ]
    }
  }
}

```

But now ... I have no idea to do that ...  
I am playing with _extended\_stats\_bucket_ but honestly I don't think are correct...

So, my question is ... **Is it possible to do this?**

In case of affirmative ... **how?**

Thanks so much in advantage

---

<div class="post-metadata">

**Author:** ![alejandrosl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alejandrosl/32/83830_2.png) [@alejandrosl](https://discuss.elastic.co/u/alejandrosl)\
**Post date:** [November 7, 2021, 8:24pm UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570/2 "2021-11-07T20:24:01Z")

</div>

Maybe I answered myself 🙂

```auto
POST my-index/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-48h",
        "lte": "now"
      }
    }
  },
  "aggs": {
    "data": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "1d"
      },
      "aggs": {
        "media": {
          "avg": {
            "field": "value_before"
          }
        },
        "difference": {
          "serial_diff": {
            "buckets_path": "media",
            "lag": 1
          }
        }
      }
    }
  }
}

```

output:

```auto
{
  "took" : 938,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "data" : {
      "buckets" : [
        {
          "key_as_string" : "2021-11-06T00:00:00.000Z",
          "key" : 1636156800000,
          "doc_count" : 3798,
          "media" : {
            "value" : 22.371458541725485
          }
        },
        {
          "key_as_string" : "2021-11-07T00:00:00.000Z",
          "key" : 1636243200000,
          "doc_count" : 22202,
          "media" : {
            "value" : 26.675546813128232
          },
          "difference" : {
            "value" : 4.3040882714027475
          }
        }
      ]
    }
  }
}

```

What do you think guys ?

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [November 8, 2021, 10:31am UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570/3 "2021-11-08T10:31:24Z")

</div>

Hi @alejandrosl

Another option you can explore is with Lens, both with the `Differences` function, or manually with Formula:

```auto
average( myField ) - average( myFilter, shift="1d")

```

The formula one will avoid to think in terms of "buckets" rather shift in terms of time, compared to the `Differences` function.

Have you tried it already?

As for your TSVB I think it looks ok to me.

---

<div class="post-metadata">

**Author:** ![alejandrosl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alejandrosl/32/83830_2.png) [@alejandrosl](https://discuss.elastic.co/u/alejandrosl)\
**Post date:** [November 8, 2021, 10:46am UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570/4 "2021-11-08T10:46:54Z")

</div>

Hi Marco !!!  
Thanks so much, I will try that 🙂 sound pretty nice

![CleanShot 2021-11-08 at 11.46.30](https://us1.discourse-cdn.com/elastic/original/3X/a/4/a41a7aa627ce784833742dabd5cf6b1265064ef7.png)

---

<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:** [December 6, 2021, 10:46am UTC](https://discuss.elastic.co/t/tsvb-metric-from-the-difference-between-avg-value-from-two-time-intervals/288570/5 "2021-12-06T10:46:55Z")

</div>

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