Calculating difference between sensor values filling gaps with last known value

Grafana InfluxDB
1
  • What Grafana version and what operating system are you using?
    10.2.2

I’ve finally built op enough of an (influxDB) dataset of sensor readings to effectively use Grafana to analyse them. Loving it so far, the power of the queries/transformations is amazing!

One thing I’m running into, is that the sensor readings are stored only on change of the value. That means, that not at all timestamps, all sensors record a value in the database.
I’m trying to visualize the subtraction of two values, and that makes the above an issue I haven’t been able to resolve. Sample table:

Time | Sensor 1 | Sensor 2 | (Transform binary s1 minus s2)
14:15 | 35 | 31 | 4
14:17 | 36 | | (none, desired is 5)
14:18 | | 30 | (none, desired is 6)
14:20 | 37 | 31 | 6

All I can manage, is that the transformation (subtraction) is only done when both sensors register at the same timestamp. When only one registers a value, no translation value is calculated/plotted.

What I would like, is that if one of the sensors doesn’t register on a timestamp, the transformation would use the last value. So in the above table, it would for sensor 2 use value 31 at 1417. And at 1418 for sensor 1 use 36.

I’ve tried grouping but can’t get the result I’m looking for. Any way to do this?

2

Welcome @selcal

Are you using Flux or InfluxQL? Can you share your query and screenshot of the transformation(s) you are using?

3

Of course!
Thanks.

I’m using Flux. The two queries:
A:
from(bucket: “Domoticz”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Temperature”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> filter(fn: (r) => r[“idx”] == “570”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

B:
from(bucket: “Domoticz”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Temperature”)
|> filter(fn: (r) => r[“idx”] == “569”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Translation so far:

image
image1462×262 18.8 KB

The output:

image
image1443×438 18.7 KB

As you can see, the only Delta Temp that’s plotted, are where both A and B have a value with the same timestamp… I’d like to use the last known value for them.

4

Can you see what happens if you modify the above (in both queries) to be:

|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

5

Thanks for thinking along. Changing it had no visible effect.

To further illustrate; an image showing the two queried values as well, with dots on values. It makes a nice visualisation:

image
image1506×395 30.2 KB

6

How about something like this? Using my own data…

Two queries:

image
image987×756 55.7 KB

and then with 3 transformations:

image
image987×838 66.7 KB

7

Thanks!
Though I can follow it now, I don’t think I would’ve come up with that anytime soon :). So much to learn still!

I did have to go for Last (not *) as otherwise it would also sometimes fill with zeroes (leading to erratic difference calculations).

Perfect like this! Many thanks!

1 Like
8

How would i solve this if I would like to use the last 2 values from a single sensor? i am using grafana with influxDB

My guess is that grafana cant do this and i need to do it with influxDB or even an mysql … right?

9

Hi @herzbeatz

It’s pretty straightforward in both InfluxQL and Flux to obtain the last value, but the second to last value is a bit trickier. The solution can be found here.

1 Like
10

Checkout tail() function

2 Likes