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

[alejandrosl (Alejandro Sánchez)]

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:

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:

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

[alejandrosl (Alejandro Sánchez)]

Maybe I answered myself

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:

{
  "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 ?

[Marco_Liberati]

Hi @alejandrosl

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

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.

[alejandrosl (Alejandro Sánchez)]

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