Dynamically adjust values to currently selected range

Grafana InfluxDB
, ,
1

Hey :slightly_smiling_face:

I have this query right now:

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sourceanalytix.0.shelly__1__shellyplus1pm#7c87ce64d6d0#1__Relay0__Energy.cumulativeReading")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 30m, fn:last)
  |> difference()
  |> yield(name: "mean")

It will give me a bar chart with the produced kwh in a 30 minute interval.

image
image1816Ă—666 13.2 KB

This works very well in a max 7 day range. But what if I want to change the dashboard to let’s say 6 months? Is there any way to dynamically adjust the “every: 30m” value to the current range?

I have really no idea yet how the flux language works… But I could think of something like this:

if range() <= 7d then 
aggregateWindow(every: 30m, fn:last)
else
aggregateWindow(every: 1d, fn:last)

Is that possible somehow?

1 Like
2

hello,

Where would you be changing this 6 months range?

3

With the time range picker of my dashboard

image
image1120Ă—640 41.2 KB

This is how it will look with a selected range of 7 days (Every: 30m):

image
image1806Ă—688 13.4 KB

I want the query to automatically switch to something like that (Every: 1d):

image
image1840Ă—674 10.7 KB

4

what if they select date ranges from here?

image

5

Doesn’t matter.
I want to adjust my query as soon as the selected range is >7 days :smiley:

1 Like
6

:laughing:

so let’s start with what the doc says and also those grafana global variables

color = if code == 0 then "green" else if code == 1 then "yellow" else "red"

Now let’s try capturing the time values from grafana in flux

7

Not sure how you can capture global variales such as $__timeFilter :frowning:

BUt you can capture your own variables

import "array"
filter = "$boojie"

a = [
    "1",
    "2",
    "3",
    "4",
    filter,
]
b = a |> array.map(fn: (x) => ({_value: x}))
array.from(rows: b)
8

I came up with something like that now:

timestart = uint(v: v.timeRangeStart)
timestop = uint(v: v.timeRangeStop)
duration = duration(v:timestop - timestart)

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sourceanalytix.0.shelly__1__shellyplus1pm#7c87ce64d6d0#1__Relay0__Energy.cumulativeReading")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: if int(v:duration) <= int(v:2d) then  30m else if int(v:duration) <= int(v:7d) then 1h else 1d , fn:last)
  |> difference()
  |> yield(name: "mean")

Works perfectly :heart_eyes:

Maybe I am able to help someone else with these problems:

First error was:

time is not Subtractable

Solution:
You have to convert the timestamp value to unix time with the uint() function

Second error:

unsupported binary expression duration < duration

Solution:
To compare two durations (e.g. 1d < 2d) you have to convert them to a numeric value first.
int(v:1d) < int(v:2d)

3 Likes
10

Great, thank you for this!

I “simplified” it further by computing a window size that always gains 800 records:

  |> aggregateWindow(every: duration(v:(uint(v: v.timeRangeStop) - uint(v: v.timeRangeStart))/uint(v: 800)), fn:last)
  |> yield(name: "mean")
1 Like