Basic math in Flux query

I am trying to create acceptable boundary range lines (e.g. 5 is the setpoint, but a range of 3.5 to 7.5 is considered allowed).

The query I have works for displaying the setpoint and actual data. How can I perform math on the setpoint (e.g. -1.5 and +1.5)?

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "actualValue" or r["_field"] == "setpointValue")
  |> filter(fn: (r) => r["tag1"] == "sensor1")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

I have tried inserting various map() functions but nothing works.

I managed to create two boundary lines (UCL and LCL) which was my original goal, but I suspect that I did this in a very clumsy way by creating 4 separate queries. Can anyone offer a more efficient way to do this? My eventual goal is to do comparisons (e.g. is Setpoint > UCL? is Setpoint < LCL?).

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "actualValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "ACTUAL")
from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "SETPOINT")
from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ r with _value: r._value +1.5 }))
  |> yield(name: "UCL")
from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ r with _value: r._value -1.5 }))
  |> yield(name: "LCL")

hallo, with your question i was just wondering how to draw line inside my data

found this in TIPS

and here for time manipulation
https://docs.influxdata.com/influxdb/cloud/query-data/flux/manipulate-timestamps/

import “experimental/array”
import “experimental”

limit = 20.0
offset = 1.0

upper_limit = limit + offset
lower_limit = limit - offset

upper = array.from(rows: [
{_measurement: “line”, _field: “DsDecimal”, _value: upper_limit, _time: now()},
{_measurement: “line”, _field: “DsDecimal”, _value: upper_limit, _time: experimental.addDuration(d: v.timeRangeStart, to: now(),)}
])
|>yield(name: “line_up”)

lower = array.from(rows: [
{_measurement: “dallas”, _field: “DsDecimal”, _value: lower_limit, _time: now()},
{_measurement: “dallas”, _field: “DsDecimal”, _value: lower_limit, _time: experimental.addDuration(d: v.timeRangeStart, to: now(),)}
])
|>yield(name: “line_down”)

Hi,
if you need everything in 1 table you can do this (but you need to check the raw data, you will have a single table with all the named columns):

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "actualValue" or r["_field"] == "setpointValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with UCL: r.setpointValue + 1.5 }))
  |> map(fn: (r) => ({ r with LCL: r.setpointValue -1.5 }))

Otherwise using your approach you should execute the query only once, put the result in a variable (data in the example) to avoid to execute multiple queries for the same data:

data = from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> yield(name: "SETPOINT")
  
data
  |> filter(fn: (r) => r["_field"] == "actualValue")
  |> yield(name: "ACTUAL")

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> map(fn: (r) => ({ r with _value: r._value +1.5 }))
  |> yield(name: "UCL")

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> map(fn: (r) => ({ r with _value: r._value -1.5 }))
  |> yield(name: "LCL")

Kind regards,
Domenico

Thank you @dsilletti and @srbp for your replies.

Domenico: The first query you wrote does indeed produce 1 single table with everything that is needed. My original question was how to produce lines (at 3.5 and 6.5), but all this table shows is a single line.

For the other query that you wrote (which uses the data variable), thank you for explaining that approach. It makes sense to me, but for some reason I am getting the error shown below. I think it has to do with the mean function in line 4?

Hi @grant1, the pivot with a single table output is not for the visualization in the dashboard but for the analytics, e.g. if you need to export the table as a python dataframe.

For the failed aggregation in the second approach, you probably have some other field containing strings and the mean aggregation function fails to aggregate all data.

In this case you must filter only for your required numeric fields: setpointValue and actualValue before aggregate, please try the following code:

data = from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "setpointValue" or r["_field"] == "actualValue")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> yield(name: "SETPOINT")
  
data
  |> filter(fn: (r) => r["_field"] == "actualValue")
  |> yield(name: "ACTUAL")

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> map(fn: (r) => ({ r with _value: r._value +1.5 }))
  |> yield(name: "UCL")

data
  |> filter(fn: (r) => r["_field"] == "setpointValue")
  |> map(fn: (r) => ({ r with _value: r._value -1.5 }))
  |> yield(name: "LCL")

Thank you Domenico for your explanation. The worker works and I now have my UCL and LCL lines. I owe you a coffee or a beer!

1 Like

Just one question (even though the graph looks perfect)…

When we put yield(name: “UCL”) in the query, that creates a column called UCL. Shouldn’t the UCL calculated value (5 + 1.5 = 7.5) appear in this column of the data?

Well, no, UCL it’s just the name of the output table, the column_result is not populated because with that code you are just replacing the values (in the column _value) of the field setpointValue with its value + or - 1.5.

We use this approach because the embedded dashboard will plot the just the _value column by default for each table (not other named columns), and this is why the pivot code I provided produces a perfect table with all named column but it’s not plotted in the chart.