# Heatmap visualization with ES|QL ignores/randomizes sorting on 2nd dimension

**URL:** <https://discuss.elastic.co/t/heatmap-visualization-with-es-ql-ignores-randomizes-sorting-on-2nd-dimension/382271>\
**Category:** Kibana\
**Created:** [September 28, 2025, 8:48pm UTC](https://discuss.elastic.co/t/heatmap-visualization-with-es-ql-ignores-randomizes-sorting-on-2nd-dimension/382271 "2025-09-28T20:48:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [September 28, 2025, 8:48pm UTC](https://discuss.elastic.co/t/heatmap-visualization-with-es-ql-ignores-randomizes-sorting-on-2nd-dimension/382271/1 "2025-09-28T20:48:18Z")

</div>

Hey,

I have an ESQL query like this:

```auto
FROM data
  | EVAL month_of_year = DATE_EXTRACT("month_of_year", date), hour_of_day = DATE_EXTRACT("HOUR_OF_DAY", date)
  | STATS AVG(value) BY month_of_year, hour_of_day
  | SORT hour_of_day ASC, month_of_year ASC

```

As this data is sorted correctly on two dimension in the ESQL response I would have expected proper sorting in my heatmap visualization as well. However the graph looks like this:

 ![grafik](https://us1.discourse-cdn.com/elastic/original/3X/a/8/a8503a4e80edbbb224415c6084648e55a6527272.png)

As you can see, the sorting of the x-axis (month of year) is completely random. If I change the sort order from `SORT hour_of_day ASC, month_of_year ASC` to `SORT month_of_year ASC, hour_of_day ASC` then the hour is mixed up randomly.

Am I missing any special option on the graph configuration? Happy to get any hints 🙂

This is on 9.1.3.

–Alex

---

<div class="post-metadata">

**Author:** ![Tortoise](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tortoise/32/147587_2.png) [@Tortoise](https://discuss.elastic.co/u/Tortoise)\
**Post date:** [September 29, 2025, 4:51am UTC](https://discuss.elastic.co/t/heatmap-visualization-with-es-ql-ignores-randomizes-sorting-on-2nd-dimension/382271/2 "2025-09-29T04:51:54Z")

</div>

Hello @spinscale

As per documentation could it be a case where the month\_of\_year ASC has NULL records because of which it is sorted last ?

> **[ES|QL commands | Elasticsearch Guide \[8.19\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.19/esql-commands.html#esql-sort)**

```auto
The SORT processing command sorts a table on one or more columns.

The default sort order is ascending. Use ASC or DESC to specify an explicit sort order.

Two rows with the same sort key are considered equal. You can provide additional sort expressions to act as tie breakers.

Sorting on multivalued columns uses the lowest value when sorting ascending and the highest value when sorting descending.

By default, null values are treated as being larger than any other value. With an ascending sort order, null values are sorted last, and with a descending sort order, null values are sorted first. You can change that by providing NULLS FIRST or NULLS LAST.

```

Thanks!!

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [October 3, 2025, 12:12pm UTC](https://discuss.elastic.co/t/heatmap-visualization-with-es-ql-ignores-randomizes-sorting-on-2nd-dimension/382271/3 "2025-10-03T12:12:20Z")

</div>

Hey,

I think that’s it. I can work around that by changing the visualization colorization in my case.

Thanks for the hint! Much appreciated!

–Alex