Flux: Monthly values of data that represents a total value

Fluxlang
, ,
1

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.

Can anybody point me in the right direction?

2

Hello @LordOfTheSnow,
I would use the following flux query:

from(bucket: "<yourbucket>"
|>range(start: <your start time>)
|> filter(fn: (r) => ...your filters)
|> aggregateWindow(every: 1mo, fn: last)
3

Thank you. I’ll give it a try.

4

No, that is not what I need.

With your query I just get the cumulated end values for each month. So just like in my example: At the end of month#1 the value is 30, at the end of month#2 the value is 50.

5

@LordOfTheSnow
Try this:

  |> aggregateWindow(every: 1mo, fn: spread)

Seems to work for me:

Constantly rising data with new value every 4-5 seconds:

image
image1797×641 45.6 KB

With above function (except every 5 minutes instead of every 1 month):
|> aggregateWindow(every: 5m, fn: spread)

image
image1560×565 52.8 KB

1 Like
6

Yes thank you. I had the same idea meanwhile and was just about to try it out. But you already confirmed it. Thanks a lot

7

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?

image
image1818×363 20.8 KB

8

Hi @LordOfTheSnow

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
9

@grant1 Thank you, but that breaks my usecase.

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.

10

Based upon your idea, I tried this:

dataSet
      |> aggregateWindow(every: 1mo, fn: mean, createEmpty: false)
      |> top (n: count() -1, columns: ["_time"])

But that doesn’t run and the docs are quite blank at that point.

error @11:15-11:22: missing required argument tables
error @11:25-11:26: expected stream[A] but found int
error @11:15-11:26: stream[A] is not Subtractable

Any idea how to reapir that?

11

@LordOfTheSnow

I think this will do the trick…

import "date"
import "timezone"

from(bucket: "your-bucket")
  |> range(start: v.timeRangeStart, stop: date.truncate(t: v.timeRangeStop, unit: 1mo))
  |> filter(fn: (r) => r["_measurement"] == "your-measurement")
  |> filter(fn: (r) => r["_field"] == "your-field")
  |> aggregateWindow(every: 1mo, fn: spread)  
  |> yield(name: "call-it-what-you-want")
1 Like
12

@grant1 I think that’s it! It works!! Thanks a lot for your help. :grinning: