Incorrect order by Term in Heat Map aggregations

[m.hashemian (Mohammad Hashemian)]

Hi there,

I have an index where each document has the following fields: record_time, group_id, user_id.

I want to create a heat map where Y-axis is the user_id, and X-axis is the date and doc count is mapped to color. I want both X and Y-axis also to be sorted (i.e. sorted user ID and sorted date). When I do this, X-axis shows the following order:

March 8th 2018
March 7th 2018
March 6th 2018
March 4th 2018
March 5th 2018
March 3rd 2018
March 2nd 2018
March 1st 2018
February 28th 2018
February 27th 2018

Note that March 4th is shown before March 5, so the items are not really sorted! I intercepted the query executed by Kibana, which looked like the following:

{
  "index": [
    "records"
  ],
  "ignore_unavailable": true,
  "preference": 1520635011103
}
{
  "size": 0,
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "terms": {
        "script": {
          "inline": "return (doc['record_time'].date.getMillis() / 86400000) * 86400000;",
          "lang": "painless"
        },
        "size": 10,
        "order": {
          "_term": "desc"
        },
        "valueType": "date"
      },
      "aggs": {
        "3": {
          "terms": {
            "field": "user_id",
            "size": 10,
            "order": {
              "_term": "asc"
            }
          }
        }
      }
    }
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {
    "record_date": {
      "script": {
        "inline": "return (doc['record_time'].date.getMillis() / 86400000) * 86400000;",
        "lang": "painless"
      }
    }
  },
  "docvalue_fields": [
  ],
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "match_phrase": {
            "group_id": {
              "query": 338
            }
          }
        },
        {
          "range": {
            "record_time": {
              "gte": 1518220800000,
              "lte": 1520639999999,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [],
      "should": [],
      "must_not": []
    }
  }
}

When I run this using cURL, the returned JSON has the correct order! Why does Kibana not show the correct order?

Thanks

[Brandon_Kobel]

Hey @m.hashemian it appears that you're using a Terms aggregation for the X-Axis against a scripted field that is formatted as a string. At this point, Kibana treats the X-Axis as a generic string, so it's not going to order them by the anticipated date. If you use a Date Histogram aggregation for the X-Axis against the record_time with the bucket size of one day, you should get what you're looking for:

[m.hashemian (Mohammad Hashemian)]

Thanks, @Brandon_Kobel. You are right. I used the Date Histogram and it worked as expected (and it makes more sense as well).