Several problems to attack, but let’s first focus on why the calculated weekly amounts are off by 1 day. How does adding the timeshift function as shown in the location below affect the results?
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -1mo)
|> timeShift(duration: -1d)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false) // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
if the above is still wrong, is it off by 1 day still, or perhaps 2 days? Maybe a positive value instead?
|> timeShift(duration: 1d)
EDIT: Another idea is to skip the timeShift function entirely and adjust the second aggregateWindow function to be as follows:
You might need to play around with the time picker, as it may try to override your start or stop time. Try to make it a wide window. Here is some test data (only contains 6 days or so) that correctly sums through today:
now “just” the labeling of the bar is wrong. Last bar should be 12.6., penultimate 5.6., …
if I apply the range-extension I get an error:
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -1mo, stop: date.truncate(t: v.timeRangeStop, unit: 1w))
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -1mo)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
The only thing I don’t like that the date of the bars is actually always one week delayed (except last bar from today). So everything should be the monday of the week start.
Alternative: Is there the possibility to display the time range covered in the bar (.e.g for the penultimate bar: 12.6. - 18.6.)??
You noted the above conclusion when playing around with the timeshift function in your query. If the only problem remaining now is the labels on the bars (i.e. one week delayed), shouldn’t this help?
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -28d, stop: date.truncate(t: v.timeRangeStop, unit: 1d))
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
|> timeShift(duration: -7d)
Which is value-wise OK, but date-wise wrong (should be 19th)
Do i have to add a second query for the current week (actually I tried to do so, but didn’t manage to make it)
What value in the Grafana times picker is timeRangeStop? I presume it’s ‘now’?
There is even a chance that tomorrow (Tue) you will get a ‘partial’ brick for the current week (starting with June 19) which should grow each day of this week.
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: date.truncate(t: v.timeRangeStop, unit: 1d))
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
But i think that one will just work today and not tomorrow …
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -28d, stop: date.truncate(t: v.timeRangeStop, unit: 1d))
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
|> timeShift(duration: -7d)
import "date"
import "timezone"
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: date.truncate(t: v.timeRangeStop, unit: 1d))
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
the 2nd flux brings the last bar, the 1st flux the other bars
Let’s try going back to one query only. Does changing the stop value in the range() function as follows help?
import "date"
import "timezone"
import "date/boundaries" // need to import this to use the next function
thisWeek = boundaries.week() // define thisWeek
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: -28d, stop: thisWeek.stop)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week
|> timeShift(duration: -7d)
Which is value-wise correct.
The question is: can a get rid of the first bar (15.5.), as it is just one day of a week and reduce it to the last full 4 weeks + the current week??
Alright @dieterlind, I think this 8+ day journey is about to cross the finish line…
How about this?
import "date"
import "timezone"
import "date/boundaries" // need to import this to use the next function
firstWeek = boundaries.week(week_offset: -4) // may need -5 here....not totally sure
thisWeek = boundaries.week() // define thisWeek
option location = timezone.location(name: "Europe/Berlin")
from(bucket: "iobroker")
|> range(start: firstWeek.start, stop: thisWeek.stop)
|> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread
|> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week, and use -3d as the offset to move the weekly aggregate to start on a Monday
|> timeShift(duration: -7d) // needed to get the weekly labels correct
