Elasticsearch returns wrong result on Date range

Elastic Stack Elasticsearch
1

My purpose is to get all result between `bill_date >= 2017-07-30 and bill_date <= 2017-08-30". But it gives me weired result.
My query is simple

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "bill_date": {
              "gte": "2017-07-31",
              "lte": "2017-08-30"
            }
          }
        },
        {
          "term": {
            "division": "FMCG FOOD"
          }
        },
        {
          "term": {
            "store_code": "1"
          }
        },
        {
          "term": {
            "icode": {
              "value": "BM7185"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "product_code": {
      "terms": {
        "field": "icode",
        "size": 3000
      },
      "aggs": {
        "salesAmt_stats": {
          "stats": {
            "field": "totalAmt"
          }
        },
        "qty_stats": {
          "stats": {
            "field": "qty"
          }
        }
      }
    }
  }
}

This query has no syntax error, and the result I get is

{
          "key": "BM7185",
          "doc_count": 2,
          "qty_stats": {
            "count": 2,
            "min": 1,
            "max": 1,
            "avg": 1,
            "sum": 2
          },
          "salesAmt_stats": {
            "count": 2,
            "min": 76,
            "max": 80,
            "avg": 78,
            "sum": 156
          }
        }

Which is clearly the wrong result, but if I change the range for bill_date to `gte:2017-07-01 and lte:2017-07-30" I get

{
          "key": "BM7185",
          "doc_count": 141,
          "qty_stats": {
            "count": 141,
            "min": 1,
            "max": 4,
            "avg": 1.049645390070922,
            "sum": 148
          },
          "salesAmt_stats": {
            "count": 141,
            "min": 75,
            "max": 320,
            "avg": 82.68794326241135,
            "sum": 11659
          }
        }
      ]
    }

Here the sum is 148 for "2017-07-01 to 2017-07-30" which is true, but why is it showing sum = 2 for 2017-07-31-2017-08-30
My schema is

"mappings": {
      "dotnet": {
        "properties": {
          "bill_date": {
            "type": "date",
            "format": "yyyy-mm-dd"
          },
          "division": {
            "type": "keyword"
          },
          "icode": {
            "type": "keyword"
          },
          "mrp": {
            "type": "double"
          },
          "qty": {
            "type": "double"
          },
          "store_code": {
            "type": "keyword"
          },
          "totalAmt": {
            "type": "double"
          }
        }
2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.