Round data calculation with limited decimals

Franck_Bellot · September 27, 2022, 12:02pm

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

scott · September 27, 2022, 2:54pm

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

Franck_Bellot · September 27, 2022, 4:24pm

@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

scott · September 27, 2022, 4:26pm

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

Franck_Bellot · September 28, 2022, 1:44pm

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

scott · September 28, 2022, 2:18pm

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

Franck_Bellot · September 28, 2022, 8:06pm

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 ?

scott · September 28, 2022, 10:26pm

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

Franck_Bellot · September 29, 2022, 6:52am

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.

Topic		Replies	Views
Decimals in a value InfluxDB 2	4	4143	November 29, 2023
Dashboard - Table - "Decimal Places" not respected InfluxDB 2	4	896	June 14, 2022
ROUND a MEAN value with a given amount of decimals? Telegraf influxdb , influxql , query	2	7562	October 23, 2019
Table does not respect custom decimal places setting InfluxDB 2	0	157	March 14, 2024
Convert rounding logic to flux Dashboards	3	33	September 22, 2024

Round data calculation with limited decimals

Related topics