# Sum in Nested Date Range Aggregation Not Handling Negative Values

**URL:** <https://discuss.elastic.co/t/sum-in-nested-date-range-aggregation-not-handling-negative-values/22482>\
**Category:** Elasticsearch\
**Created:** [March 2, 2015, 11:01pm UTC](https://discuss.elastic.co/t/sum-in-nested-date-range-aggregation-not-handling-negative-values/22482 "2015-03-02T23:01:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nate\_Nash](https://avatars.discourse-cdn.com/v4/letter/n/ee59a6/32.png) [@Nate\_Nash](https://discuss.elastic.co/u/Nate_Nash)\
**Post date:** [March 2, 2015, 11:01pm UTC](https://discuss.elastic.co/t/sum-in-nested-date-range-aggregation-not-handling-negative-values/22482/1 "2015-03-02T23:01:02Z")

</div>

Hello -

I have a an index with 30M documents, spread across 4 nodes, 30 shards,  
with 2 replicas each. Each document has a nested object, representing a  
transaction log entry. Each transaction log entry has a positive or  
negative dollar value and a timestamp. When I attempt to bucket the nested  
objects using the date range and sum aggregations, the sum aggregation  
appears to break on transactions with both positive and negative numbers.

For example, I use the follow query. I am including a reverse nested agg to  
show that the nested objects are different than the output of the sum agg.  
Enter code here

"size": 0,  
"query": {  
"filtered": {  
"query": {  
"match\_all": {}  
},  
"filter": {  
"bool": {  
"must": [  
{  
"term": {  
"foo.\_id": "12345"  
}  
},  
{  
"range": {  
"timestamp": {  
"gte": "2011-10-01"  
}  
}  
},  
{  
"term": {  
"bar.name": "A Special Name"  
}  
}  
]  
}  
}  
}  
},  
"aggs": {  
"BAR": {  
"terms": {  
"field": "bar.name",  
"size": 2,  
"order": {  
"NESTED\>TOTAL": "asc"  
}  
},  
"aggs": {  
"NESTED": {  
"nested": {  
"path": "a"  
},  
"aggs": {  
"TOTAL": {  
"sum": {  
"field": "a.obligatedamount"  
}  
},  
"DATES": {  
"date\_range": {  
"field": "a.signeddate",  
"keyed": true,  
"ranges": [  
{  
"key": "FY2012",  
"from": "2011-10-01",  
"to": "2012-09-30"  
},  
{  
"key": "FY2013",  
"from": "2012-10-01",  
"to": "2013-09-30"  
},  
{  
"key": "FY2014",  
"from": "2013-10-01",  
"to": "2014-09-30"  
},  
{  
"key": "FY2015",  
"from": "2014-10-01",  
"to": "2015-09-30"  
}  
]  
},  
"aggs": {  
"DATEBUCKET\_SUBTOTAL": {  
"sum": {  
"field": "a.obligatedamount"  
}  
},  
"HITS\_REVERSE" : {  
"reverse\_nested": {},  
"aggs": {  
"HITS": {  
"top\_hits": {  
"\_source": {  
"include": [  
"a.obligatedamount",  
"a.signeddate"  
]  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}...

The following result is returned. Note the odd number in the  
DATEBUCKET\_SUBTOTAL in FY2012 for "A Special Place". This appears to only  
be an issue when the transaction log contains both negative and positive  
numbers.

{  
"took": 11,  
"timed\_out": false,  
"\_shards": {  
"total": 30,  
"successful": 30,  
"failed": 0  
},  
"hits": {  
"total": 2,  
"max\_score": 0,  
"hits": []  
},  
"aggregations": {  
"BAR": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"key": "A Special Place",  
"doc\_count": 2,  
"NESTED": {  
"doc\_count": 3,  
"DATES": {  
"buckets": {  
"FY2012": {  
"from": 1317427200000,  
"from\_as\_string": "2011-10-01T00:00:00.000Z",  
"to": 1348963200000,  
"to\_as\_string": "2012-09-30T00:00:00.000Z",  
"doc\_count": 1,  
"HITS\_REVERSE": {  
"doc\_count": 1,  
"HITS": {  
"hits": {  
"total": 1,  
"max\_score": 1,  
"hits": [  
{  
"\_index": "myIndex",  
"\_type": "myType",  
"\_id": "AG9J61P110025",  
"\_score": 1,  
"\_source": {  
"a": [  
{  
"signeddate":  
"2011-02-01T00:00:00+0000",  
"obligatedamount": 4000  
},  
{  
"signeddate":  
"2012-07-11T00:00:00+0000",  
"obligatedamount": -1694  
}  
]  
}  
}  
]  
}  
}  
},  
"DATEBUCKET\_SUBTOTAL": {  
"value": -8.365e-321 \<\<-- This appears to be  
incorrect. Shouldn't it be 2306?  
}  
},  
"FY2013": {  
"from": 1349049600000,  
"from\_as\_string": "2012-10-01T00:00:00.000Z",  
"to": 1380499200000,  
"to\_as\_string": "2013-09-30T00:00:00.000Z",  
"doc\_count": 0,  
"HITS\_REVERSE": {  
"doc\_count": 0,  
"HITS": {  
"hits": {  
"total": 0,  
"max\_score": null,  
"hits": []  
}  
}  
},  
"DATEBUCKET\_SUBTOTAL": {  
"value": 0  
}  
},  
"FY2014": {  
"from": 1380585600000,  
"from\_as\_string": "2013-10-01T00:00:00.000Z",  
"to": 1412035200000,  
"to\_as\_string": "2014-09-30T00:00:00.000Z",  
"doc\_count": 1,  
"HITS\_REVERSE": {  
"doc\_count": 1,  
"HITS": {  
"hits": {  
"total": 1,  
"max\_score": 1,  
"hits": [  
{  
"\_index": "myIndex",  
"\_type": "myType",  
"\_id": "AG04GGP140011",  
"\_score": 1,  
"\_source": {  
"a": [  
{  
"signeddate":  
"2013-12-19T00:00:00+0000",  
"obligatedamount": 3449  
}  
]  
}  
}  
]  
}  
}  
},  
"DATEBUCKET\_SUBTOTAL": {  
"value": 3449  
}  
},  
"FY2015": {  
"from": 1412121600000,  
"from\_as\_string": "2014-10-01T00:00:00.000Z",  
"to": 1443571200000,  
"to\_as\_string": "2015-09-30T00:00:00.000Z",  
"doc\_count": 0,  
"HITS\_REVERSE": {  
"doc\_count": 0,  
"HITS": {  
"hits": {  
"total": 0,  
"max\_score": null,  
"hits": []  
}  
}  
},  
"DATEBUCKET\_SUBTOTAL": {  
"value": 0  
}  
}  
}  
},  
"TOTAL": {  
"value": 3449  
}  
}  
}  
]  
}  
}  
}..

My mapping looks like so:  
{  
"myIndex": {  
"mappings": {  
"myType": {  
"dynamic\_templates": [  
{  
"basestring": {  
"mapping": {  
"index": "not\_analyzed"  
},  
"match": "\*",  
"match\_mapping\_type": "string"  
}  
}  
],  
"properties": {  
"a": {  
"type": "nested",  
"properties": {  
"obligatedamount": {  
"type": "double"  
},  
"signeddate": {  
"type": "date",  
"format": "dateOptionalTime"  
}  
}  
},  
"bar": {  
"properties": {  
"\_id": {  
"type": "string",  
"index": "not\_analyzed"  
},  
"name": {  
"type": "string",  
"index": "not\_analyzed"  
},  
"type": {  
"type": "string",  
"index": "not\_analyzed"  
}  
}  
},  
"timestamp": {  
"type": "date",  
"format": "dateOptionalTime"  
},  
"foo": {  
"properties": {  
"\_id": {  
"type": "string",  
"index": "not\_analyzed"  
},  
"name": {  
"type": "string",  
"index": "not\_analyzed"  
},  
"type": {  
"type": "string",  
"index": "not\_analyzed"  
}  
}  
}  
}  
}  
}  
}  
}

Many thanks in advance for any help you can provide.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/437b01fb-8a9e-4bcd-b047-6ed568cea0ab%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/437b01fb-8a9e-4bcd-b047-6ed568cea0ab%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 12:29am UTC](https://discuss.elastic.co/t/sum-in-nested-date-range-aggregation-not-handling-negative-values/22482/2 "2017-07-06T00:29:00Z")

</div>