Breakdown metric by formula result

[Nithin_Ramesh]

Hey,

I am trying to visualize some data, but I have a feeling that I may be trying to do something that is impossible in Kibana currently. I have data that logs each event in a shipment process that consists of moving 10-20 loads over several stages (picked up, in transit, dropped off). I would like to analyze the total time it took from the first load that was initiated to when the last load was placed as movements per hour and shipment and compare it to a standard metric of 18.3 movements per shipment per hour. I have succeeded in creating this as a data table as you see in the screenshot below.

I calculate the different fields as follows:
Trains:
unique_count(labels.tsuid)
Actual:
(max(@timestamp, kql='custom-metric-name.keyword : "DropOff Completed" ')-min(@timestamp, kql='custom-metric-name.keyword : "Transport Created" '))/(1000*60)
Required:
unique_count(labels.tsuid)*60/18.3
Gap:
unique_count(labels.tsuid)*60/18.3 - (max(@timestamp, kql='custom-metric-name.keyword : "DropOff Completed" ')-min(@timestamp, kql='custom-metric-name.keyword : "Transport Created" '))/(1000*60)

What I would like to do is group by percentage the ratio of the different gaps for all the shipment ids, something like this (taken from an excel graph):

Using v 8.5.3

Any tips?

[Andrew_Tate (Drew Tate)]

Hi @Nithin_Ramesh , unfortunately Lens cannot do this. The main reason is that the arithmetic in that formula you're using to compute the "Gap" occurs in your browser after Elasticsearch has already run the aggregations (e.g. bucketing into the bar chart you depicted) on the server.

In other words, Elasticsearch can't produce those numbers for your bar chart based on the results of Lens formulas because it has no knowledge of them.

We are actively working on adding a new query language called ESQL. When it lands, it should cover this and many other cases since all the arithmetic you define in ESQL will be performed by Elasticsearch.

[Nithin_Ramesh]

Hey @Andrew_Tate ! Thought as much. Looking forward to ESQL! Thanks for the reply!