Subtract First Point From Time Series In Fluxlang

,
1

Hello, I am really new to influxdb so I don’t really know what I am actually doing with flux, please bear with me :slight_smile:

I have measurements and I’d like to subtract the first value of the grafana observation window from the whole measurements.

I got two flux statements that work to get the measurements and to get the first value according to the grafana observation window but I am not able to subtract that first value from all the measurements so that the grafana-plot starts at 0:

data = from(bucket: "myinfluxdb/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "water" and r._field == "volume_l")
  |> yield(name: "data")

// Retrieve start value
start_value = data
  |> sort(columns: ["_time"], desc: false) 
  |> limit(n: 1) 
  |> rename(columns: {_value: "start_value"})
  |> yield(name: "start_value")

What I’d now like to do, in simple words, is: data - start_value
Can someone help me please? Thank you!

2

@nocsspecop While it’s a different approach, I think increase() will give you the result that you’re looking for:

from(bucket: "myinfluxdb/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "water" and r._field == "volume_l")
  |> increase()
3

Nice! Way simpler than the stuff I tried to do and it works! Thank you for the fast reply!

4

@scott your solution actually works for a “normal” grafana time series.
If i configure the grafana time series as a “stack series 100%” the graphical representation goes bonkers, i suppose there is a problem with the time-axis (x-axis) → I am using multiple flux-queries to do the “percentage stacking”

Is there a possibility to do the subtraction by using the two values I extracted with the flux statemens from my first post? I’d like to try if that solves the graphical glitches.

Thanks!

5

@nocsspecop The problem with the first approach is that it isn’t really possible to identify what start_value in the start_value stream of tables belongs to each series/group/table in the data stream of tables. It may be possible with some kind of join, but even then, it would still be tricky.

I’m wondering if it may be something with how the visualization is configured in Grafana. Each of them have a ton of configuration options that let you customize them in ways that may make it work better with the data as-is.

6

@scott I was thinking maybe its related to the fact that for the first timestamp, all the different measurements start with 0 and so grafana isn’t able to “stack” them and has problems to visualize the plot over time. Is there a way to let the measurements start with 1 (instead of 0) so that for the first timestamp the stacked plots are equally distributed (e.g. with 4 measurements it would be 25%-25%-25%-25%)?

7

Ok, I found a way to let the timeseries start with 1, it did not solve the problem. But your solution with “increment” is ok if I don’t stack the timeseries, so everything is ok. Thank you!

1 Like