Flux with integral

[laurentrioux91]

• Grafana version : 10.1

I’m trying to display pie chart and bar graph with the same data & values
The pie chart will represent the imported kwh of the current day and the bar graph the imported kwh for the last 14 days (day by day)

here my flux for the pie:
from(bucket: “mydbase/autogen”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “MqttBridge”)
|> filter(fn: (r) => r.device == “48E729199708”)
|> filter(fn: (r) => r._field == “IMPORT”)
|> fill(column: “_value”, usePrevious: true)
|> integral(unit: 1h)
|> cumulativeSum()
|> keep(columns: [“_value”, “_time”])
|> rename(columns: {_value: “Import”})
|> yield(name:“Import”)

My bar Graph flux:
import “date”

from(bucket: “mydbase/autogen”)
|> range(start: -15d, stop: -1d)
|> filter(fn: (r) => r._measurement == “MqttBridge”)
|> filter(fn: (r) => r.device == “48E729199708”)
|> filter(fn: (r) => r._field == “IMPORT”)
|> fill(column: “_value”, usePrevious: true)
|> aggregateWindow(every: 1h, fn: (tables=<-, column) =>
(tables
|> integral(unit: 1h)
|> map(fn: (r) =>
({r with _value: r._value}))))
|> truncateTimeColumn(unit:1d)
|> group(columns: [“_time”], mode: “by”)
|> sum(column: “_value”)
|> group()
|> rename(columns: {_value: “Import”})

the problem: When I select for example 26 June: I compare the Pie Value and the Bar graph value of that day they are not equal !! Pie: 2.03 kwh and bar graph: 1.83 kwh.

Where is the error ?

Thanks if you can help me.

[ebabeshko]

You are using different queries with different ranges / logic etc., so it’s hard to answer your question without knowing your schema and seeing data that you have.

May be you can use the same query for both visualizations? That is possible with --Dashboard-- data source:

[laurentrioux91]

Thank you for your help. So, I use the same query for both visualisations, but now I have an issue: I do not know how to truncate value per day to create the bar graph.

The query I did,is creating a table where for each hour I got the kwh and this for the last 14 days (hour by hour). Now I would like to sum them per each day (to get the total kwh per day) to fill the bar graph. Any idea to do that ?

To be more precise, how to use transform rules to do it from the data source of the dashboard ?

[ebabeshko]

@laurentrioux91

Now I would like to sum them per each day (to get the total kwh per day)

Try something like this:

 |> aggregateWindow(every: 1d, fn: sum, createEmpty: false, timeSrc: "_start")

See also this thread with similar discussion:

Influxdb2: Combining last + sum to get weekly values

What Grafana version and what operating system are you using? Grafana 9.3.2 What are you trying to achieve? Visualize in a bar graph the weekly consumption based on daily values for one month How are you trying to achieve it? import “timezone” option location = timezone.location(name: “Europe/Berlin”) from(bucket: “iobroker”) |> range(start: -1mo, stop: now()) |> filter(fn: (r) => r[“_measurement”] == “HausverbrauchKwh” and r[“_field”] == “value”) |> aggregateWindow(every: 24h, f…

[laurentrioux91]

The problem is: I do not have access to flux (no possibility to write flux query) because I’m using a dashboard as data source. I can only specify “transform” rules.

Except I miss something, if so tell me how to write flux query when a dashboard is selected as data source?

[laurentrioux91]

Now, I found the issue. I think there is a bug in grafana flux about timezone.

when I used pie chart with timezone (europe/Paris) and the bargraph with timezone (europe/london), so -1h difference.

I got the same results. But If I put europe/Paris in the bargraph query the computation is not correct.
I think the issue coming with the command:

aggregateWindow(every: 1d, fn: sum, timeSrc: “_start”)

for the pie I do not know this instruction, I just use Sum() since I would like to get to the total of the day.

So to fix it: I need to put london time in my bargraph and paris time in my pie chart. So, when I check what ever the day, both bargraph and pie vhart provide me the same result.

so it is more a workaround than a solution