# Lens visualisation doesn't sort on formula result **URL:** \ **Category:** Kibana\ **Tags:** lens\ **Created:** [February 4, 2022, 7:27am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258 "2022-02-04T07:27:20Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Dominik11](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@Dominik11](https://discuss.elastic.co/u/Dominik11)\ **Post date:** [February 4, 2022, 7:27am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258/1 "2022-02-04T07:27:20Z")
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](http://transaction.duration.us) (this is APM data) - Split (series) based on service.name (which holds the API name) Something like this: ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/6/96d1c27699baf7761d1a35d0ad51f408e979dd3e.png) Doing it like this works and I get the expected results. However, I find this visualisation to be less readable because [transaction.duration.us](http://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](http://transaction.duration.us). **Without using a formula** I'm able to sort the results based the average transaction duration: ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/5/35c9288723517456447f17ebbf33482c39d3a1b3.png) **When the formula average([transaction.duration.us](http://transaction.duration.us))/1000** is applied this isn't possible anymore: ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/3/8308f3d6b006eaff89cc982451641092b1129cbf.png) 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 ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [February 4, 2022, 9:29am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258/2 "2022-02-04T09:29:11Z")
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](https://github.com/elastic/kibana/issues/114951) ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [February 4, 2022, 10:02am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258/3 "2022-02-04T10:02:24Z")
> [@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. ---
**Author:** ![Dominik11](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@Dominik11](https://discuss.elastic.co/u/Dominik11)\ **Post date:** [February 4, 2022, 10:25am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258/4 "2022-02-04T10:25:19Z")
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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 4, 2022, 10:26am UTC](https://discuss.elastic.co/t/lens-visualisation-doesnt-sort-on-formula-result/296258/5 "2022-03-04T10:26:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.