I have a zigbee plug that offers a data point of the total energy consumed, e.g. of my TV.
Example: At the beginning of the first month the value is 0 kw/h, at the end of the first month the value is 30 kw/h. At the beginning of the second month the value is 30 kw/h, at the end of the second month the value is 50 kw/h.
What I would like to display in Grafana is 30 kwh/h for the first month and 20 kw/h for the second month.
I am struggling in writing an approprate Flux query for this.
When I have an aggregateWindow of one month, it works smoothly for all previous months. But for the current month I then have two values (one for the current moment when the query runs). Is there any way to suppress this last value?
There is probably a more elegant way to do this, but I think this would work:
|> sort(columns: ["_time"], desc: true) // sorts records by _time and puts them in the opposite order that they are appearing. This means the one you want to discard is at the top
|> limit(n:10, offset: 1) // Limit results to the first 10 rows in the table after the first one, i.e. the one you want to discard
The number of results to be displayed should only be limited by the time frame I set in Grafana. So if set it to one year, 12 results should be displayed, if I set it to six months, six results should be displayed. And they should be in ascending order of course.