# Rounding error in lens sum aggregation (8.12.1)

**URL:** <https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141>\
**Category:** Kibana\
**Tags:** lens, aggregations\
**Created:** [March 11, 2024, 9:36am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141 "2024-03-11T09:36:36Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 11, 2024, 9:36am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/1 "2024-03-11T09:36:36Z")

</div>

I have the same three values reoccurring every 10 seconds as shown in Discover:

(Here was a picture, I couldn't post, because I'm a new user.)

The sum should add up to 1.642. This also shows in lens in an area visualization:

(Here was a picture, I couldn't post, because I'm a new user.)

You see the simple sum aggregation over one (redacted) field. Inspecting the data, also shows the right value of 1.642 (No rounding or formatting is defined.).

(Here was a picture, I couldn't post, because I'm a new user.)

But looking at the request response, I see a rounding error:

 ![kibana_bug_4](https://us1.discourse-cdn.com/elastic/original/3X/b/1/b1b5aa0a30238f791272926fabd1c2880b91d719.png)  
The request that is send is:

```auto
POST /_redacted_*/_async_search?batched_reduce_size=64&ccs_minimize_roundtrips=true&wait_for_completion_timeout=200ms&keep_on_completion=true&keep_alive=60000ms&ignore_unavailable=true&preference=1710140294656
{
  "aggs": {
    "0": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "10s",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 1
      },
      "aggs": {
        "1": {
          "sum": {
            "field": "prometheus.metrics._redacted_"
          }
        }
      }
    }
  },
  "size": 0,

```

This error in the data becomes obvious when trying to get the derivative:

(Here was a picture, I couldn't post, because I'm a new user.)

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 11, 2024, 9:39am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/2 "2024-03-11T09:39:40Z")

</div>

Here is the screenshot from Discover of the three values.

 ![kibana_bug_1](https://us1.discourse-cdn.com/elastic/original/3X/e/9/e955d7c146dc7e6939f3cfc89b9f51b9134b1888.png)

I'm unsure, If I should post the other screenshots in replies one at a time, because I don't want to break any forum rules.  
If someone can give me the appropriate rights, I can edit my initial post.

---

<div class="post-metadata">

**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\
**Post date:** [March 11, 2024, 3:25pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/3 "2024-03-11T15:25:12Z")

</div>

Hi @Jim_Panzee — welcome to the community.

I'm sorry you had trouble uploading screenshots; I'm not familiar with the forum rules for new users.

Let me make sure I understand your question, though.

It sounds like the correct value of `1.642` is showing in Lens everywhere, but then you see the trailing `000...001` in the response in the inspector. Is that right?

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 11, 2024, 7:40pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/4 "2024-03-11T19:40:39Z")

</div>

Hi Andrew. Thanks for your fast response.

This is correct. But if I change the formula by adding a difference aggregation in the front, the wrong values also show up in lens:

 ![kibana_bug_5](https://us1.discourse-cdn.com/elastic/original/3X/8/2/825852b05c1d9e39223570a8e2908e4f4fe6ac19.png)  
As you can see, since the wrong value alternates with the correct value this tiny derivation shows in the diagram with e-16.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [March 11, 2024, 7:51pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/5 "2024-03-11T19:51:35Z")

</div>

@Jim_Panzee

In Discover, open up the document and look at the actual JSON and Fields see if they all show the exact values...

Discover does some niceties in the table sometimes...

I ran into something like this a while back and see if I can find it... that turned out to be actually a JSON issue... Floating Point representation in JSON...

I will see if I can find the issue I saw before ... may not be the same but was similar.

> [@Float type rounding issues](https://discuss.elastic.co/t/float-type-rounding-issues/321859/6):
>
> (please try not to paste text as images... hard to see, help, debug etc...etc. paste as formatted text) But YUP You need to use a double as the type not float as you are exceeding the significant digits of a single precision float Also you are confusing the \_source (what comes in the source json) and the fields what is index stored and showed in the Discover, Visualizations.. etc Here follow along What you have now PUT discuss-test/ { "mappings" : { "properties": { "@timestamp": { …

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 12, 2024, 7:05am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/6 "2024-03-12T07:05:45Z")

</div>

Thank you for your help Stephen,

I checked both. The json in Discover shows the correct values, e.g.:

> "prometheus.metrics._redacted_": [  
> 0.084  
> ],

I also looked at our mapping for this field and it is double.

> "prometheus.metrics._redacted_": {  
> "type": "double"  
> },

---

<div class="post-metadata">

**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:** [March 12, 2024, 7:55am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/7 "2024-03-12T07:55:55Z")

</div>

Mind that a `double` type has still a rounding error which can reflect in that `00...0001` when performing math operations over fractional values.

If you click `Open in Console` within the Inspector request panel and execute the query, does it return this rounding issue? If so, then the source of the problem is in the mappings and Elasticsearch.

One possible workaround I can suggest is, if you know already that your values always have a 3 digits precisions max, is to configure a 3 digits number format within the Lens visualization.

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 12, 2024, 9:01am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/8 "2024-03-12T09:01:56Z")

</div>

Hi Marco and thank you for your response. When I execute the aggregation in the console, I get the same occasional error. In my case the number only has four digits and I make a simple sum aggregation. I think this should not run into any double rounding errors since I don't calculate fractions.

I already thought of the workaround with a round aggregation, but wanted to report the bug anyway. When you say it is a problem on the elastic side, do I have to change the tags on this post?

Result of the aggregation execution in the console:

```auto
"aggregations": {
      "0": {
        "buckets": [
          {
            "1": {
              "value": 1.642
            },
            "key_as_string": "2024-03-08T11:30:50.000+01:00",
            "key": 1709893850000,
            "doc_count": 1150
          },
          {
            "1": {
              "value": 1.6420000000000001
            },
            "key_as_string": "2024-03-08T11:31:00.000+01:00",
            "key": 1709893860000,
            "doc_count": 1146
          },
          {
            "1": {
              "value": 1.6420000000000001
            },
            "key_as_string": "2024-03-08T11:31:10.000+01:00",
            "key": 1709893870000,
            "doc_count": 1146
          },
          {
            "1": {
              "value": 1.6420000000000001
            },
            "key_as_string": "2024-03-08T11:31:20.000+01:00",
            "key": 1709893880000,
            "doc_count": 1146
          },
          {
            "1": {
              "value": 1.642
            },
            "key_as_string": "2024-03-08T11:31:30.000+01:00",
            "key": 1709893890000,
            "doc_count": 1147
          },

```

---

<div class="post-metadata">

**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:** [March 12, 2024, 9:07am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/9 "2024-03-12T09:07:19Z")

</div>

It is not a bug, it's just how a `double` number is rounded in Java when fractional.

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 12, 2024, 9:12am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/10 "2024-03-12T09:12:35Z")

</div>

Ok, than I don't understand some basics.  
If I run a sum aggregation like:

> sum(1.01, 0.084, 0.548)

How can the result sometimes become 1.6420000000000001  
and sometimes 1.642 ?

Btw. this did not happen before we upgraded to 8.12.

---

<div class="post-metadata">

**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:** [March 12, 2024, 9:49am UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/11 "2024-03-12T09:49:21Z")

</div>

This depends on many things (i.e. java version used by ES who got upgraded, change of other internals, etc...) but in general doing a `sum` of fractional numbers is always subject to this kind of rounding issues.

A `Java` double is similar on how numbers [are handled in JS](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Numbers_and_dates). If you try in the console `1.01 + 0.084 + 0.548` then it returns `1.6420000000000001`. In the link above it explains a bit how double precision numbers work in JS, but the formula is pretty similar in Java too.  
As said, Java uses a similar representation but not identical, so there are stil subtle changes between the two.

Using a `float` in the mapping will lead to a less precision (7 fractional digits vs 15 of `double`), but it does not guarantee that the problem won't appear at the 7th digit.  
Using a `integer` in the mapping is the approach often used when dealing with scenarios where precision is required. In this case values are scaled (i.e. \* 1000 if 3 digits are required) when stored and scaled back when represented. An example use case of this is when dealing with money ( cents representation \* 100 stored, then cents/100 back to visualize).

---

<div class="post-metadata">

**Author:** ![Jim\_Panzee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jim_panzee/32/132508_2.png) [@Jim\_Panzee](https://discuss.elastic.co/u/Jim_Panzee)\
**Post date:** [March 12, 2024, 1:25pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/12 "2024-03-12T13:25:37Z")

</div>

Thank you for your explanation. As you can imagine, this is not very satisfying, as we now have to implement the "round aggregation"-workaround into the failing diagrams. I had hopes to get the old behavior back.

---

<div class="post-metadata">

**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:** [March 12, 2024, 1:59pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/13 "2024-03-12T13:59:38Z")

</div>

You can try to ask into the Elasticsearch section, maybe they can provide an alternative approach, as there are some special handles I may not know there.

---

<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:** [April 9, 2024, 1:59pm UTC](https://discuss.elastic.co/t/rounding-error-in-lens-sum-aggregation-8-12-1/355141/14 "2024-04-09T13:59:46Z")

</div>

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