I’m not getting the output I am expecting regardless of if Grafana does a Transform data or Influx does the math for me. I have two queries that I want to do math on to determine a percentage.
What I am looking for is the sum of the solar generation over the last week (Query A), divided by the sum of the house usage over the last week (Query B), multiplied by 100 to get my solar offset percentage. This is working perfectly for my daily Grafana panel, but when I look at the last 7 days, the data doesn’t appear or is completely off.
Grafana 11.2.3
Influx 2.7.11
Query A: by itself yields 447 (at that moment in time)
import "timezone"
option location = timezone.location(name: "America/Los_Angeles")
from(bucket: "home-assistant")
|> range(start: -7d, stop: now())
|> filter(fn: (r) => r["_measurement"] == "W")
|> filter(fn: (r) => r["entity_id"] == "solar_power_total")
|> filter(fn: (r) => r["_field"] == "value")
|> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> truncateTimeColumn(unit: 1h)
|> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start", createEmpty: true)
|> group() // Group all resulting rows into a single group
|> sum(column: "_value") // Sum the _value column of the single group
|> rename(columns: {_value: "solar"})
Query B: by itself yields 301 (at that moment)
import "timezone"
option location = timezone.location(name: "America/Los_Angeles")
from(bucket: "home-assistant")
|> range(start: -7d, stop: now())
|> filter(fn: (r) => r["_measurement"] == "W")
|> filter(fn: (r) => r["entity_id"] == "house_power_total")
|> filter(fn: (r) => r["_field"] == "value")
|> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> truncateTimeColumn(unit: 1h)
|> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start", createEmpty: true)
|> group() // Group all resulting rows into a single group
|> sum(column: "_value") // Sum the _value column of the single group
|> rename(columns: {_value: "usage"})
The Grafana panel will show “solar” with a value of say 447 and “usage” with a value of 301. When I add a Transform data, (Add field from calculation) using Binary mode and with the Operation as “solar” / “usage”, nothing happens. I was expecting 1.48, but nothing is displayed regardless of if I give an Alias or Replace all fields. If I add another binary transformation to multiple by 100, the Alias or output results aren’t shown, as expected from the previous lack of output in the first transformation.
When I asked this question to AI, it gave me a completely different query, stating to do the transformations within the query, but the results from that query are WAY off.
AIs query suggestion:
import "timezone"
option location = timezone.location(name: "America/Los_Angeles")
solar = from(bucket: "home-assistant")
|> range(start: -7d, stop: now())
|> filter(fn: (r) => r["_measurement"] == "W")
|> filter(fn: (r) => r["entity_id"] == "solar_power_total")
|> filter(fn: (r) => r["_field"] == "value")
|> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> truncateTimeColumn(unit: 1h)
|> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start", createEmpty: true)
|> rename(columns: {_value: "solar"})
usage = from(bucket: "home-assistant")
|> range(start: -7d, stop: now())
|> filter(fn: (r) => r["_measurement"] == "W")
|> filter(fn: (r) => r["entity_id"] == "house_power_total")
|> filter(fn: (r) => r["_field"] == "value")
|> map(fn: (r) => ({ r with _value: r._value / 1000.0 }))
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)
|> truncateTimeColumn(unit: 1h)
|> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start", createEmpty: true)
|> rename(columns: {_value: "usage"})
join(tables: {solar: solar, usage: usage}, on: ["_time"])
|> map(fn: (r) => ({ _time: r._time, solar: r.solar, usage: r.usage, offset: if r.usage > 0.0 then (r.solar / r.usage) * 100.0 else 0.0 }))
Is this an issue with summing up the last 7 days? Or perhaps with the group () in Queries A and B?