dert94
April 19, 2023, 4:42pm
1
What Grafana version and what operating system are you using?
9.4.7

What are you trying to achieve?
I want to use a calculated field in an expression

How are you trying to achieve it?
I have two queries

```
from(bucket: "ioBroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW")
|> filter(fn: (r) => r["_field"] == "value")
|> elapsed(unit: 1s)
|> map(fn: (r) => ({ r with _value: (float(v: r._value) / 1000.0) * (float(v: r.elapsed) / 3600.0) })) // formula
|> sum()
|> yield(name: "value")
```

and

```
from(bucket: "ioBroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
|> filter(fn: (r) => r["_field"] == "value")
|> elapsed(unit: 1s)
|> map(
fn: (r) => ({r with
_value: if r._value <= 0.0 then
0.0
else
(float(v: r._value)/1000.0) * (float(v: r.elapsed) / 3600.0),
}),
)
|>sum()
|> yield(name: "value")
```

I would like to subtract the result of the two queries from each other with a transformation.

But the field names also contain the time span. Thus the calculation works only within the time span

grant2
April 20, 2023, 9:37am
2
Welcome @dert94 to the Grafana forum.

How about (in InfluxDB) doing a join and then doing the mathematical calculation in the joined table? I would work this out in Influx Data Explorer (with view Raw Data enabled) and once you have it working there, paste it into Grafana.

dert94
April 20, 2023, 10:34am
3
Hey,

I am not quite sure how you mean it. Can you explain it a little bit more?

In Influx I get a similar result with and without “elapsed”. Only without the calculation

In this test, I noticed that sum() causes the same thing.

Table View in Grafana

grant2
April 21, 2023, 1:48am
4
Hi @dert94

You would have two tables (t1 and t2, representing your two queries) and then do the math in the joined table. Something like this should work:

```
t1 = from(bucket: "ioBroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW")
|> filter(fn: (r) => r["_field"] == "value")
|> elapsed(unit: 1s)
|> map(fn: (r) => ({ r with _value: (float(v: r._value) / 1000.0) * (float(v: r.elapsed) / 3600.0) })) // formula
|> sum()
|> drop(columns: ["_start", "_stop"])
t2 = from(bucket: "ioBroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
|> filter(fn: (r) => r["_field"] == "value")
|> elapsed(unit: 1s)
|> map(
fn: (r) => ({r with
_value: if r._value <= 0.0 then
0.0
else
(float(v: r._value)/1000.0) * (float(v: r.elapsed) / 3600.0),
}),
)
|> sum()
|> drop(columns: ["_start", "_stop"])
join(
tables: {t1: t1, t2: t2},
on: ["_time", "_measurement"],
method: "inner"
)
|> map(fn: (r) => ({_time: r._time, _measurement: r._measurement, _difference: r._value_t1 - r._value_t2}))
|> yield(name: "difference")
```

dert94
April 23, 2023, 8:07am
5
Hi,

I have tried your query. But after the join I get no data.
T1 and T2 work well when I try them separately.
The join returns nothing.
You are using the “_time” column for the join. But in the output of t1 and t2 there is no “-time” column.

But I was able to solve it in a different way.
The field name is still wrong but it currently works.

```
import "contrib/tomhollingworth/events"
from(bucket: "ioBroker")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW" or r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
|> filter(fn: (r) => r["_field"] == "value")
|> events.duration(unit: 1s)
|> map(
fn: (r) => ({r with
_value: if r._value <= 0.0 then
0.0
else
(float(v: r._value)/1000.0) * (float(v: r.duration) / 3600.0),
}),
)
|> sum()
|> pivot(rowKey: ["_field"], columnKey: ["_measurement"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r["senec.0.ENERGY.GUI_HOUSE_POW"]-r["senec.0.ENERGY.GUI_GRID_POW"] }))
|> drop(columns: ["senec.0.ENERGY.GUI_HOUSE_POW","senec.0.ENERGY.GUI_GRID_POW"])
```