Sum/diff calculated for values discriminated by tags

Fluxlang
1

Hello,
I’m new to flux language and have a maybe naive question:

I’m using InfluxDB to monitor energy consumptions.
To do so a number of energy counters are installed, one for total consumption (Imp: imported energy, Exp for generated Energy, solar: for PV-power).
The energy counter show values rising with time, to calculate consumption you need to calculate difference of e.g. 15min intervals.
To be able to calculate Energy balance correctly, generated energy has a negative sign, consumed energy a positive

Data is stored in one measurement “Energy”, discriminated by the tag “sens”.
I now want to calculate one missing value, equation like: “E(Exp)+E(Imp)-E(solar)”

If I understand fluxQL correctly, the required math (negative Esolar) is not possible so I turned to flux.

I figured out most of the steps:

from(bucket: "iobroker")

|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == “Energy”)
|> filter(fn: (r) => (r.sens == “Imp”) or (r.sens == “Exp”) or (r.sens == “solar”))
|> map(fn: (r) => ({ r with _value:
if r.sens == “solar” then (-1.0)*r._value else r._value
}))
|> window(every: 15m)
|> last()
|> duplicate(column: “_stop”, as: “_time”)
|> group(columns: [“sens”], mode:“by”)
|> difference(nonNegative: false, columns: [“_value”])
|> drop(columns:[“room”,“channel”,“sens”])

At the end of this script table is as expected:

grafik
grafik1102×412 26.6 KB

I thought I’m now only two steps away from the desired result:
-Group by the already aggregated timestamps (one row of data per sensor already aligned to the 15min timestamps)
-calculate sum of those values

Tried:

|> group(columns: [“_time”], mode:“by”)
|> sum(column:“_value”)

But the server generates an error: “runtime error: invalid memory address or nil pointer dereference on sum()”.
I’m running influxdb on a raspberry Pi 3.

I would be grateful for any recommendation !

1 Like
2

Hello @TimTom,
Is there anyway you can send me a portion of your data? Maybe you can use the to csv button in the UI?
Alternatively, can you please share a screen shot of the data before the last drop?
Thanks!

3

Hi @Anaisdg,
I’ve extracted real life data from Chronograf.

First step; query:

from(bucket: “iobroker”)
|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == “Energy”)
|> filter(fn: (r) => (r.sens == “Imp”) or (r.sens == “Exp”) or (r.sens == “solar”))
|> map(fn: (r) => ({ r with _value:
if r.sens == “solar” then (-1.0)*r._value else r._value
}))

results are as expected: Chronograf_2.txt (13.0 KB)

Second step: almost final query:

from(bucket: “iobroker”)
|> range(start: dashboardTime)
|> filter(fn: (r) => r._measurement == “Energy”)
|> filter(fn: (r) => (r.sens == “Imp”) or (r.sens == “Exp”) or (r.sens == “solar”))
|> map(fn: (r) => ({ r with _value:
if r.sens == “solar” then (-1.0)*r._value else r._value
}))
|> window(every: 15m)
|> last()
|> duplicate(column: “_stop”, as: “_time”)
|> group(columns: [“sens”], mode:“by”)
|> difference(nonNegative: false, columns: [“_value”])
|> drop(columns:[“room”,“channel”,“sens”])

results still ok: Chronograf_3.txt (63.8 KB)
Between one and three lines per timestamps, the column sens was stripped.
Those values should be summed up per timestamp.