Noob influxdb2/flux arithmetic question

Support
#1

Greetings,

I’ve got my Iotawatt units feeding measurements into InfluxDBv2, but I haven’t quite wrapped my head around the basics of this Flux query language yet.

I have two CTs named I1_DTED1_L1 and I2_DTED1_L2. I would like to plot each of these CT Watt measurements, as well as the sum of both CT Wattages.

I can plot the two measurements with:

from(bucket: “iotawatt”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“ct”] == “I1_DTED1_L1” or r[“ct”] == “I2_DTED1_L2”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

But I have no idea how to compute the sum of these two values and plot them as a 3rd plot on the chart even after doing some reading. Can anyone point me in the right direction here?

Thanks in advance!

#2

The easiest thing to do would be to add another measurement in the influx setup screen to combine the two CTs into one measurement.

1 Like
#3

Did you mean the Iotawatt setup screen, or the Influx setup screen?

If you meant Iotawatt, that does work well when both circuits are monitored by the same Iotawatt unit. But I am going to want to do arithmetic between circuits on two different Iotawatt units, so I think I will need to figure out how to do this at the Influx layer.

If you did mean the Influx setup screen, can you elaborate on what you mean / which screen you’re talking about?

#4

I can gladly help. I am traveling right now but when I get home I can help you with Math

It is fairly easy once you understand the process

#5

Sorry I didn’t have notifications turned on. Yes, I meant in the iotawatt screen. Yes, you are right if they are measured by two different you have to do it in the Influx layer.

I am not good with influx so I cannot help you there.

#6

i forgot about this thread, i am back from raveling, do you still need assistance? i can post some of my FLUX language queries as examples for you

#7

That would be great, I would really appreciate that. Thanks!

#8

here is an example from a dashboard i am actively using

from(bucket: "home_energy")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r.Mains1_W + r.Mains2_W }))
|> keep(columns: ["_value", "_time"])
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> rename(columns: {_value: "Total Power Draw"})

in this, i am pulling data from my bucket home_energy

i am getting my two MAINS watts Mains1_W and Mains2_W

the purpose of this query is to graph total power draw of the house, so i need to add the two measurements together

before i can do this, i need to use the pivot command

|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

you can see more details on pivot here

now i can actually perform the desired math function where i add the two values together

|> map(fn: (r) => ({ r with _value: r.Mains1_W + r.Mains2_W }))

you can see more here

and go down to the area titled ** Transform values in a data stream**

the next line allows me to keep the data i care about, and get rid of the rest

|> keep(columns: ["_value", "_time"])

i need this because i want the _value and time data for the graph, but i no longer need the data in the Mains1_W and Mains2_W

finally i need to control the window size so when i change the time window in grafana the graph will update accordingly. i also want to rename the _value field to something a little more “user friendly”

|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> rename(columns: {_value: "Total Power Draw"})
1 Like
#9

here is another example

import "strings"

month = time(v: "${strings.substring(v: string(v: now()), start: 0, end: 8)}01T00:00:00Z")

from(bucket: "home_energy")
|> range(start: month, stop: now())
|> filter(fn: (r) => r._field == "Mains1_W" or r._field == "Mains2_W")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: (r.Mains1_W + r.Mains2_W) * 0.14552 }))
|> aggregateWindow(
    every: 1mo,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Total Energy Uage Per Day", _time: "Date"})

i have the math line

|> map(fn: (r) => ({ r with _value: (r.Mains1_W + r.Mains2_W) * 0.14552 }))

to calculate the cost of my energy usage by multiplying it by the cost per Kwh.

however the data is not yet in the form of a Kwh so i need to do more advanced math by performing calculus

|> aggregateWindow(
    every: 1mo,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))

here i am telling the system to get 1 months worth of data by specifying the 1mo
i am telling it to integrate per hour (because we want kilo-watt-hour using |> integral(unit: 1h)

finally i need to divide by 1000 to change watts into kilo-watts

|> map(fn: (r) => ({ r with _value: r._value / 1000.0})))

one key aspect of this is, if you noticed i have two separate math functions, one after the other. that is perfectly fine if you wish and in this case was required.

1 Like
#10

One comment for energy, it seems to work best if you create a separate bucket for kWh values via a task. I run one every hour, and then have a second task that is similar to re-do the integration weekly in case the system is offline for one interval. I’ve also had to manually re-integrate windows occassionally with a task; it doesn’t add data since it is the same timestamp and value.

option task = {name: "IotaIntegral-Hourly", cron: "0 * * * *", offset: 1m}

data = from(bucket: "Iotabucket2")
	|> range(start: -1h)
	|> filter(fn: (r) =>
		(r["_field"] == "Watts"))
	|> aggregateWindow(every: 1h, fn: (tables=<-, column) =>
		(tables
			|> integral(unit: 1h)
			|> map(fn: (r) =>
				({r with _value: r._value / 1000.0, _field: "kWh", units: "kWh"}))))

data
	|> to(bucket: "IotaHourly")
1 Like
Recommended Influxdb2 & Grafana setup?
#11

Oh! i did not know about scheduled tasks. this will be helpful in generating plots of data usage over several years. right now when i try plotting data usage per month over the course of several years it fails due to the integration taking too long.

with this, if i integrate over 1 day within a task, and then integrate the new data generated by the task, that will significantly reduce the processing needed.

thanks!