# Round data calculation with limited decimals

**URL:** <https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735>\
**Category:** InfluxDB 2\
**Created:** [September 27, 2022, 12:02pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735 "2022-09-27T12:02:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Franck\_Bellot](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/franck_bellot/32/10162_2.png) [@Franck\_Bellot](https://community.influxdata.com/u/Franck_Bellot)\
**Post date:** [September 27, 2022, 12:02pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/1 "2022-09-27T12:02:32Z")

</div>

Hello,

I got many support from @scott to be able to build my final query.  
That’s my first one, it’s a good opportunity to get minimum know-how.

I see in my calculation that my float number has more than 2 decimals.  
I see many tricks to be able to keep only 2 decimals but my syntax looks not good.

Can you confirm to me what should be the formula to be able to get only 2 decimals

below exemple formula of my query as an exemple

> |\> map(fn: (r) =\> ({ r with BRHCJB\_Conso: if exists r.BBRHCJB\_Max then (r.BBRHCJB\_Max - r.BBRHCJB\_Min) / 1000.0 else 0.0}))

_See my full Query if it can help somebody to get cumulative Tip & tricks provided by @scott 😉_

```auto
data = () =>
    from(bucket: "DB_Tele")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(
            fn: (r) =>
                r["_measurement"] == "BBRHCJB" or r["_measurement"] == "BBRHCJR" or r["_measurement"] == "BBRHCJW"
                    or
                    r["_measurement"] == "BBRHPJB" or r["_measurement"] == "BBRHPJR" or r["_measurement"] == "BBRHPJW"
                    or
                    r["_measurement"] == "HCHC" or r["_measurement"] == "HCHP",
        )
        |> filter(fn: (r) => r["_field"] == "Compteur")

 max = data()
        |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
        //|> fill(value: 0.0)
        |> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
        |> rename(
            fn: (column) => {
                columnRegex = /HCHC|HCHP|BBRHCJB|BBRHCJW|BBRHCJR|BBRHPJB|BBRHPJW|BBRHPJR/
                columnName = if column =~ columnRegex then "${column}_Max" else column
                return columnName
            },
        )

 min = data()
        |> aggregateWindow(every: 1d, fn: min, createEmpty: false)
        //|> fill(value: 0.0)
        |> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
        |> rename(
            fn: (column) => {
                columnRegex = /HCHC|HCHP|BBRHCJB|BBRHCJW|BBRHCJR|BBRHPJB|BBRHPJW|BBRHPJR/
                columnName = if column =~ columnRegex then "${column}_Min" else column
                return columnName
            },
        )
        
join(tables: {min: min, max: max}, on: ["_time"])
|> drop(columns: ["_field_max","_field_min","_start_max","_start_min","_stop_max","_stop_min"])

|> map(fn: (r) => ({ r with BRHCJB_Conso: if exists r.BBRHCJB_Max then (r.BBRHCJB_Max - r.BBRHCJB_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with BRHPJB_Conso: if exists r.BBRHPJB_Max then (r.BBRHPJB_Max - r.BBRHPJB_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with BRHCJW_Conso: if exists r.BBRHCJW_Max then (r.BBRHCJW_Max - r.BBRHCJW_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with BRHPJW_Conso: if exists r.BBRHPJW_Max then (r.BBRHPJW_Max - r.BBRHPJW_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with BRHCJR_Conso: if exists r.BBRHCJR_Max then (r.BBRHCJR_Max - r.BBRHCJR_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with BRHPJR_Conso: if exists r.BBRHPJR_Max then (r.BBRHPJR_Max - r.BBRHPJR_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with HCHC_Conso: if exists r.HCHC_Max then (r.HCHC_Max - r.HCHC_Min) / 1000.0 else 0.0}))
|> map(fn: (r) => ({ r with HCHP_Conso: if exists r.HCHP_Max then (r.HCHP_Max - r.HCHP_Min) / 1000.0 else 0.0}))

|> map(fn: (r) => ({ r with STAT_TODAY_HP: r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso}))
|> map(fn: (r) => ({ r with STAT_TODAY_HC: r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso}))
|> map(fn: (r) => ({ r with STAT_TODAY: r.STAT_TODAY_HP + r.STAT_TODAY_HC}))

|> timeShift(duration: -1s, columns: ["_time"])
|> truncateTimeColumn(unit: 1d)

|> keep(fn: (column) => column =~ /_Conso|STAT_TODAY|_time/)

```

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/f/f64ec9f23dd3f261c08a48b384a56252877a1754.png)

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [September 27, 2022, 2:54pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/2 "2022-09-27T14:54:12Z")

</div>

@Franck_Bellot Here’s one approach, but it’s not fool proof. Arithmetic operations on floating point values can result in unexpected rounding, so you may still get many numbers after the decimal. But this approach essentially multiplies the float value by 100.0, uses math.round() to round it to the nearest whole number, then divides it by 100.0. That way you _should_ be left with only two decimal points, but it’s not guaranteed with floating point arithmetic.

Note: I also updated your query a little bit to minimize the `map()` calls and reuse the same regular expressions to filter your measurements:

```javascript
import "math"

measurementRegex = /HCHC|HCHP|BBRHCJB|BBRHCJW|BBRHCJR|BBRHPJB|BBRHPJW|BBRHPJR/

data = () =>
    from(bucket: "DB_Tele")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r._measurement =~ measurementRegex)
        |> filter(fn: (r) => r["_field"] == "Compteur")

max =
    data()
        |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
        |> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
        |> rename(
            fn: (column) => {
                columnName = if column =~ measurementRegex then "${column}_Max" else column

                return columnName
            },
        )

min =
    data()
        |> aggregateWindow(every: 1d, fn: min, createEmpty: false)
        |> pivot(rowKey: ["_time"], columnKey: ["_measurement"], valueColumn: "_value")
        |> rename(
            fn: (column) => {
                columnName = if column =~ measurementRegex then "${column}_Min" else column

                return columnName
            },
        )

join(tables: {min: min, max: max}, on: ["_time"])
    |> drop(
        columns: [
            "_field_max",
            "_field_min",
            "_start_max",
            "_start_min",
            "_stop_max",
            "_stop_min",
        ],
    )
    |> map(
        fn: (r) => ({ r with
            BRHCJB_Conso: if exists r.BBRHCJB_Max then (r.BBRHCJB_Max - r.BBRHCJB_Min) / 1000.0 else 0.0,
            BRHPJB_Conso: if exists r.BBRHPJB_Max then (r.BBRHPJB_Max - r.BBRHPJB_Min) / 1000.0 else 0.0,
            BRHCJW_Conso: if exists r.BBRHCJW_Max then (r.BBRHCJW_Max - r.BBRHCJW_Min) / 1000.0 else 0.0,
            BRHPJW_Conso: if exists r.BBRHPJW_Max then (r.BBRHPJW_Max - r.BBRHPJW_Min) / 1000.0 else 0.0,
            BRHCJR_Conso: if exists r.BBRHCJR_Max then (r.BBRHCJR_Max - r.BBRHCJR_Min) / 1000.0 else 0.0,
            BRHPJR_Conso: if exists r.BBRHPJR_Max then (r.BBRHPJR_Max - r.BBRHPJR_Min) / 1000.0 else 0.0,
            HCHC_Conso: if exists r.HCHC_Max then (r.HCHC_Max - r.HCHC_Min) / 1000.0 else 0.0,
            HCHP_Conso: if exists r.HCHP_Max then (r.HCHP_Max - r.HCHP_Min) / 1000.0 else 0.0,
        }),
    )
    |> map(
        fn: (r) => ({
            STAT_TODAY_HP: r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso,
            STAT_TODAY_HC: r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso,
            STAT_TODAY: r.STAT_TODAY_HP + r.STAT_TODAY_HC,
        }),
    )
    |> map(
        fn: (r) => {
            _round = (x) => math.round(x: x * 100.0) / 100.0

            return {r with
                BRHCJB_Conso: _round(x: r.BRHCJB_Conso),
                BRHPJB_Conso: _round(x: r.BRHPJB_Conso),
                BRHCJW_Conso: _round(x: r.BRHCJW_Conso),
                BRHPJW_Conso: _round(x: r.BRHPJW_Conso),
                BRHCJR_Conso: _round(x: r.BRHCJR_Conso),
                BRHPJR_Conso: _round(x: r.BRHPJR_Conso),
                HCHC_Conso: _round(x: r.HCHC_Conso),
                HCHP_Conso: _round(x: r.HCHP_Conso),
                STAT_TODAY_HP: _round(x: r.STAT_TODAY_HP),
                STAT_TODAY_HC: _round(x: r.STAT_TODAY_HC),
                STAT_TODAY: _round(x: r.STAT_TODAY),
            }
        },
    )
    |> timeShift(duration: -1s, columns: ["_time"])
    |> truncateTimeColumn(unit: 1d)
    |> keep(fn: (column) => column =~ /_Conso|STAT_TODAY|_time/)

```

The only full-proof option would be to convert the floats to strings and trim the decimal points off at two, but this won’t do any rounding.

---

<div class="post-metadata">

**Author:** ![Franck\_Bellot](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/franck_bellot/32/10162_2.png) [@Franck\_Bellot](https://community.influxdata.com/u/Franck_Bellot)\
**Post date:** [September 27, 2022, 4:24pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/3 "2022-09-27T16:24:39Z")

</div>

@scott , I got error message

> error @58:29-58:33: undefined identifier math error @56:8-74:6: record is missing label HCHP\_Conso (argument tables)’

If needed to know, I’m using the release InfluxDB v2.4.0

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/b/bb3e1574c9ce45296c27c881edffd1f1ed2e11bc.png)

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [September 27, 2022, 4:26pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/4 "2022-09-27T16:26:58Z")

</div>

You need to import the `math` package at the beginning of your script.

---

<div class="post-metadata">

**Author:** ![Franck\_Bellot](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/franck_bellot/32/10162_2.png) [@Franck\_Bellot](https://community.influxdata.com/u/Franck_Bellot)\
**Post date:** [September 28, 2022, 1:44pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/5 "2022-09-28T13:44:22Z")

</div>

I did know that we need to import package to run Math feature, but easy to do 😉

Only one aspect  
When I use your function, below query error message appear, I do not understand why “\_Time label” is missing because not removed

```auto
 |> map(
        fn: (r) => ({
            STAT_TODAY_HP: r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso,
            STAT_TODAY_HC: r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso,
            STAT_TODAY: r.STAT_TODAY_HP + r.STAT_TODAY_HC,
        }),
    )

```

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/9/9a377783b1e6387ef71211eae8fcaf5c445acd58.png)

If I use my previous syntax all is working well

```auto
|> map(fn: (r) => ({ r with STAT_TODAY_HP: math.roundtoeven(x: (r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso) * 100.0) / 100.0 }))
|> map(fn: (r) => ({ r with STAT_TODAY_HC: math.roundtoeven(x: (r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso) * 100.0) / 100.0 }))
|> map(fn: (r) => ({ r with STAT_TODAY: math.round(x: (r.STAT_TODAY_HP + r.STAT_TODAY_HC) * 100.0) / 100.0 }))

```

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/a/ab65afd6d6a937e38f8481c83626a1c505044b83.png)

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [September 28, 2022, 2:18pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/6 "2022-09-28T14:18:46Z")

</div>

Oh, whoops. That was my fault. `_time` actually does get removed in the map call because, as is, it doesn’t _extend_ the row record. It replaces it. To extend the row record, add `r with` in the mapped record:

```javascript
 |> map(
        fn: (r) => ({ r with
            STAT_TODAY_HP: r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso,
            STAT_TODAY_HC: r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso,
            STAT_TODAY: r.STAT_TODAY_HP + r.STAT_TODAY_HC,
        }),
    )

```

---

<div class="post-metadata">

**Author:** ![Franck\_Bellot](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/franck_bellot/32/10162_2.png) [@Franck\_Bellot](https://community.influxdata.com/u/Franck_Bellot)\
**Post date:** [September 28, 2022, 8:06pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/7 "2022-09-28T20:06:56Z")

</div>

Thanks @scott

Sorry but still an error (another one)

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/7/7a4b026c2e989379dc8aa8f24fac69fb4f586d82.png)

I have identified the line which provides the error, the following one

> STAT\_TODAY: r.STAT\_TODAY\_HP + r.STAT\_TODAY\_HC,

Is it because r.STAT\_TODAY\_HP and r.STAT\_TODAY\_HC are created just before into the same map() call ?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [September 28, 2022, 10:26pm UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/8 "2022-09-28T22:26:07Z")

</div>

@Franck_Bellot Ah, yep, you’re right. You could do an additional map call or just calculate `STAT_TODAY` in the same map call:

```javascript
 |> map(
        fn: (r) => ({ r with
            STAT_TODAY_HP: r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso,
            STAT_TODAY_HC: r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso + r.HCHC_Conso,
            STAT_TODAY: (r.BRHPJB_Conso + r.BRHPJW_Conso + r.BRHPJR_Conso + r.HCHP_Conso) + (r.BRHCJB_Conso + r.BRHCJW_Conso + r.BRHCJR_Conso),
        }),
    )
```

---

<div class="post-metadata">

**Author:** ![Franck\_Bellot](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/franck_bellot/32/10162_2.png) [@Franck\_Bellot](https://community.influxdata.com/u/Franck_Bellot)\
**Post date:** [September 29, 2022, 6:52am UTC](https://community.influxdata.com/t/round-data-calculation-with-limited-decimals/26735/9 "2022-09-29T06:52:24Z")

</div>

That is what I thought, I just need confirmation 😉

Thanks again @scott for your support, I have a real good start to well manage my next queries.