Lens visualisation doesn't sort on formula result

[Dominik11 (Dominik)]

Hi all,

I have been recreating some of my dashboards using Lens visualisations but I stumbled on a seemingly illogical limitation. The concept of one visualisation is like this : I have 50+ API's running and I want to see the Top-10 API's with the highest average response time over time.

This visualisation is created with:

• X-axis = Date histogram
• Y-axis = Average of transaction.duration.us (this is APM data)
• Split (series) based on service.name (which holds the API name)

Something like this:

Doing it like this works and I get the expected results. However, I find this visualisation to be less readable because transaction.duration.us is expressed in micro-seconds and I prefer to see milli-seconds. In my old visualisation I used to work around this by supplying a script in the JSON-input of the series bucket, but with Lens that doesn't exist anymore and the Formula feature seemed the way to go.

However, once the formula is applied (divide by 1000), the series become only sortable alphabetically on service.name and no longer sortable on (the result of dividing) transaction.duration.us.

Without using a formula I'm able to sort the results based the average transaction duration:

When the formula average(transaction.duration.us)/1000 is applied this isn't possible anymore:

Logical to say that the latter doesn't show me relevant information. I tried forcing the format of the formula result to be "Number" but that doesn't change anything.

Any thoughts, solutions, ... ?

Regards,
Dominik

[Marco_Liberati]

Hi @Dominik11

welcome to the Kibana community.
We're aware of this limit, there's already an issue you can track progress on this problem here: https://github.com/elastic/kibana/issues/114951

[Marco_Liberati]

@Dominik11

Doing it like this works and I get the expected results. However, I find this visualisation to be less readable because transaction.duration.us is expressed in micro-seconds and I prefer to see milli-seconds. In my old visualisation I used to work around this by supplying a script in the JSON-input of the series bucket, but with Lens that doesn't exist anymore and the Formula feature seemed the way to go.

Another approach you can have is to set a duration formatter on the transaction.duration.us field via the indexpattern management page.

[Dominik11 (Dominik)]

Thanks for that remark. That work-around does the trick also ... for now I'll use that while waiting for the Lens bug to be fixed which would be the prefered solution.
I have other scenarios where this wouldn't be applicable (ex. calculating JVM memory usage as a percentage). To work around that I'll consider mapping a runtime field.