# Round data calculation with limited decimals

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

``````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/)
``````

@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:

``````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.

@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

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

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

`````` |> 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,
}),
)
``````

If I use my previous syntax all is working well

``````|> 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 }))
``````

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:

`````` |> 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,
}),
)
``````

Thanks @scott

Sorry but still an error (another one)

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 ?

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

`````` |> 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),
}),
)``````

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.