TIme series Visual builder : How to do Math calculations betweent the output of two series?

[Harpreet_Singh]

Hi Team,

I am using Time series visual builder: Top N Chart, i am in a scenario where i am doing Math calculation in 5 different series. See below Screenshot:

Pic 1: Final Chart
Pic 2: Different time series i have choosen
Pic 3: 1 of the time series where i am doing math calculations.

Question: Each of the series calculates the number of hours saved by a particular automation process, what i need is a way to do sum on the output of these 6 series so that i can get the total time saved by all processes.

As of now i cannot seem to find a way to do this in time series visual builder, can we do it in this chart or is this a functionality available in another chart?

Thank you
Harpreet Singh

[flash1293 (Joe Reuter)]

It's not possible to do cross-series calculations in TSVB. I don't know how your other series look like, but if they are similar, you might be able to collapse them all into one series.

Are you using different filters for each of those? If that's not the case, you can probably add all of the metric aggregations in a single series and then have one large "math" aggregation in the bottom doing all of the calculations at once.

[Harpreet_Singh]

Hi @flash1293

Yeah thats exactly the problem, all my series work on different filters hence different data set altogether, so clubbing them all in one wont be possible.

If this is not possible in TSVB, then can we do something like this in any other visualization type?

Let me elaborate a bit more on what i ma trying to do so that you get more perspective:
I want to do 5 calculations(arithmetic equation is different for all 5) on 5 different filters(that gives 5 different dataset) and later i want to do a sum on the output of all previous 5.

I hope this clears it a bit more

Thank you
Harpreet Singh

[flash1293 (Joe Reuter)]

We are currently working on supporting these kinds of calculations in Lens, but it will be at least two minor versions till we get there.

Till then, I think you will have to fall back to Vega: Learn to build rich Vega Visualizations in Kibana | Elastic Blog It allows you to query Elasticsearch directly using the query DSL which allows these kinds of calulcations. A combination of Bucket script aggregation | Elasticsearch Reference [7.11] | Elastic and Filter aggregation | Elasticsearch Reference [7.11] | Elastic should do the trick.

[Harpreet_Singh]

Thanks @flash1293
Appreciate it!!

[flash1293 (Joe Reuter)]

Vega and Elasticsearch DSL are a large topic, feel free to update this thread if you get stuck somewhere.

[Harpreet_Singh]

Hi @flash1293 ,
Thanks for the nudge towards Vega, i am going through the docs and it seems this might give us what we need.

While working with this i have stumbles upon an another issue, hope you can help.
I have a scenario where i have to take the cardinality aggregation on an String field and then multiply the output of the cardinality field by an integer. See below request for reference: But seems like i cant do that in kibana, i have tried using the IntegerParse( ) methods too but no luck there as well. Any ideas how i can achive that?

[Harpreet_Singh]

Hi @flash1293
Can you help me with the above?

[flash1293 (Joe Reuter)]

Hey, sorry for the late reply. This example should contain all the things you need, let me know whether it helps:

POST /*logs/_search
{
  "size": 0,
  "aggs": {
    "groups": {
      "filters": {
        "filters": {
          "all": {
            "match_all": {}
          }
        }
      },
      "aggs": {
        "US": {
          "filter": {
            "term": {
              "geo.src": "US"
            }
          },
          "aggs": {
            "uniqueips": {
              "cardinality": {
                "field": "clientip"
              }
            }
          }
        },
        "factored_ips": {
          "bucket_script": {
            "buckets_path": {
              "usUniqueIps": "US>uniqueips"
            },
            "script": "params.usUniqueIps * 2"
          }
        }
      }
    }
  }
}

It uses a separate bucket_script agg to do the calculation on top of the cardinality aggregation. To do that, you what to wrap the whole thing into a "filters" agg

[Harpreet_Singh]

Thanks again @flash1293!!