Use previous values if there are no new values

Grafana InfluxDB
,
1

Hi,
im using Grafana 10.1.2 and FluxQL (influxDB2)

I’m Trying to display the State of Charge from my home battery in a Time-Series Panel using FluxQL
Here is my Query:

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Battery(SoC)")
  |> filter(fn: (r) => r["_field"] == "value")

If there is no new value, the last part of the SoC in the Panel is empty. When the SoC changes again, the line connects to the end (because there’s a new value).

When using influxDB instead of FluxQL there’s an option called fill(previous).
Is there something similar to fill(previous) in fluxQL or do you have other solutions?

Thanks,
Daniel

2

Welcome @danielkofler

Have you tried the last function? last() returns the last row with a non-null value from each input table.

3

with last() I get (as you said) the last value. For the time-series-panel I need more than one value.

Here is my example 1 (Battery Power):
The Battery was full around 13:00. Now it’s 13:46 and the line doesn’t continue, because there’s now powerflow into or out of the battery and because of that the value hasn’t been updated since 13:00.

image
image2192×566 34.7 KB

Here is my example 2 (State of Charge):
It’s the same with the state of charge. When i reach 100%, there are no more values written into the influxdb. So the line stops at 12:00.

image
image2158×530 37.6 KB

Here is the same example with influxDB1.x, where I can use the fill(previous):

image
image2168×532 41.7 KB

image
image1082×518 48.7 KB

Something similar for FluxQL is the solution I’m looking for :wink:

4

Thanks for explaining further.

I have never used this function, but give it a try:

|> fill(usePrevious: true)
5

there’s no effect when adding |> fill(usePrevious: true) :frowning:

6

Any solution on this topic? I run in a similar one: https://community.grafana.com/t/no-data-if-last-influxdb-entry-is-out-of-range/107457

7

Best way I found so far: (in fluxDB)

Do a sort of pre-fetch by selecting a slightly larger period, using the fill(useprevious)
And then reducing te selection again to the original range.

import “date”

from(bucket: “Insights”)
|> range(start: date.sub(d: 24h, from: v.timeRangeStart), stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_field”] == “measure_humidity”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: true)
|> fill(usePrevious: true)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> yield(name: “last”)

Seems to work for the voids at the beginning of the period and also at the end.