Search code examples
elasticsearchkibanadashboardmetrics

Trends metric on Kibana Dashboard, it’s possible?

I want to create a metric in kibana dashboard, which use ratio of multiple metrics and offset period.

Example :

Date Budget

YYYY-MM-DD $

2019-01-01 15

2019-01-02 10

2019-01-03 5

2019-01-04 10

2019-01-05 12

2019-01-06 4

If I select time range between 2019-01-04 to 2019-01-06 , I want to compute ratio with offset period: 2019-01-01 to 2019-01-03.

to resume : (sum(10+12+4) - sum(15+10+5)) / sum(10+12+4) = -0.15 evolution of my budget equal to -15% (and this is what I want to print in the dashboard)

But, with metric it's not possible (no offset), with visual builder: different metric aggregation do not have different offset (too bad because bucket script allow to compute ratio), and with vega : I not found a solution too.

Any idea ? Thanks a lot

Aurélien

NB: I use kibana version > 6.X

Solution

  • Please check the below sample mapping which I've constructed based on data you've provided in the query and aggregation solution that you wanted to take a look.

    Mapping:

    PUT <your_index_name>
{
  "mappings": {
    "mydocs": {
      "properties": {
        "date": {
          "type": "date",
          "format": "yyyy-MM-dd"
        },
        "budget": {
          "type": "float"
        }
      }
    }
  }
}

    Aggregation

    I've made use of the following types of aggregation:

    Also I'm assuming that the date format would be in yyyy-MM-dd and budget would be of float data type.

    Below is how your aggregation query would be.

    POST <your_index_name>/_search
{
  "size": 0,
  "query": {
    "range": {
      "date": {
        "gte": "2019-01-01",
        "lte": "2019-01-06"
      }
    }
  },
  "aggs": {
    "my_date": {
      "date_histogram": {
        "field": "date",
        "interval": "4d",
        "format": "yyyy-MM-dd"
      },
      "aggs": {
        "sum_budget": {
          "sum": {
            "field": "budget"
          }
        },
        "budget_derivative": {
          "derivative": {
            "buckets_path": "sum_budget"
          }
        },
        "budget_evolution": {
          "bucket_script": {
            "buckets_path": {
              "input_1": "sum_budget",
              "input_2": "budget_derivative"
            },
            "script": "(params.input_2/params.input_1)*(100)"
          }
        }
      }
    }
  }
}

    Note that the result that you are looking for would be in the budget_evolution part.

    Hope this helps!