Total Consumption summed up Last 7 days (not working)

Grafana Bar Gauge Panel
, ,
1

Hello,
I’m relatively new to Grafana and have been sitting for 2 days now with a problem that I just can’t get on with.
It should be relatively simple, but somehow I do not get it.
I have an electricity meter reading, per “Bar gauge” I would like to display the consumption of the last 7 days (total consumption in 7 days).
If I look at the data per:

|> aggregateWindow(every: 1d, fn: last, timeSrc: "_start", offset: 4d)
|> difference()

then the data is correct.
But as soon as I enter 7d, only the consumption of today is displayed.

import "timezone
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "data")
  |> range(start: -14d, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "Kühlschrank.ENERGY_Total")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 7d, fn: last, timeSrc: "_start", offset: 4d)
  |> difference()
  |> yield(name: "last")

My expectation was to see the consumption summed up (Last 7 days) 11.10.2022 0:00 - 17.10.2022 now()
So 4,144 kWh

graf2
graf21472×1354 131 KB

2

When windowing by week (1w), weeks are determined using the Unix epoch (1970-01-01T00:00:00Z UTC). The Unix epoch was on a Thursday, so all calculated weeks begin on Thursday.

1w = yours 7d, so aggregatewindow doesn’t group by your last 7 days, but by Unix epoch weeks - they start on Thursday, in your case 2022-10-16 and 2022-10-13 + you are using offset 4d, so you are “moving” these week 4 days ahead.

IMHO you don’t need aggregatewindow - you need only single value for last 7 days, not a timeseries.

3

Thank you very much for your explanations.

IMHO you don’t need aggregatewindow - you need only single value for last 7 days, not a timeseries.

Your comment / solution approach is still not quite clear to me how I should implement.
Do you have another tip on how I should implement this? I would like to solve this as in the picture over a “bar gauge”, since later several measuring points are to be integrated.

4

My Solution:

data = from(bucket: "iobroker") 
|> range(start: -7d) 
|> filter(fn: (r) 
  => r["_measurement"] == "Spülmaschine_Gosund.ENERGY_Total"
  or r["_measurement"] == "MultimediaWHZ.ENERGY_Total"
  or r["_measurement"] == "Trockner_Gosund1.ENERGY_Total")
|> filter(fn: (r) => r["_field"] == "value")
min = data 
|> min() 
|> set(key: "_field", value: "delta")
max = data 
|> max() 
|> set(key: "_field", value: "delta")
union(tables: [min, max])
|> difference()
1 Like