Reducing Series to First Value

[emilioserra]

What Grafana version and what operating system are you using?
v11.1.0 on a raspberry debian bookworm

Maybe this is too simple to see a straightforward solution: I would like to display the balance (last - first value) of a battery soc in a certain time range.
I managed to get the last value by “reduce fileds” → last, but there is no option for the first value?!
Next try was to do the reduction in the influxdb query itself:

from(bucket: “evcc_log”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “batterySoc”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> filter(fn: (r) => r[“id”] == “1”)
|> aggregateWindow(every: v.windowPeriod, fn: first, createEmpty: false)
|> yield(name: “first”)

But despite yield(name: “first”) the influxdb query delivers the total series?!

So, what do to?

[yosiasz]

thats more of a flux question than a grafana question

How to get difference of last() and first() values in the influx measurement

I have grafana setup with influx. I can run some general queries in the influx console which i’m finding hard to mimic the same in grafana. Influx Query : select last(count) - first(count) as Count from measurement1 In grafana i’m able to select last() or first() with ease and not getting how to get the difference of it. Grafana Version - 6.4.0 [image]

[emilioserra]

I had already read that discussion before, but I am sorry, I can’t find a solution for my problem there.

[yosiasz]

again it is not a grafana issue. best to ask on influx forum if no one else chimes in here

Get the difference between the last and the first value

[ebabeshko]

@emilioserra

Next try was to do the reduction in the influxdb query itself:

That could be done by two flux queries. See this example:

Aggregate _Value for each Min and Max timestamp of that value

One of possible ways to do that is to have two queries, one to get Min_Time and another to get Max_Time for every IP. Sample query A: from(bucket: "TEST") |> range(start: -1mo, stop: v.timeRangeStop) |> filter(fn: (r) => r._measurement == "log" and r._field == "message" and r.host == "grafana.host.de" |> group(columns:["_value"]) |> first() |> group() |> keep(columns:["_time", "_value"]) |> rename(columns: {_time : "Min_Time", _value : "I…

[emilioserra]

Thanks so much, again especially to @ebabeshko.
The solution for me was quite simple:

> from(bucket: "evcc_log")
>   |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
>   |> filter(fn: (r) => r["_measurement"] == "batterySoc")
>   |> filter(fn: (r) => r["_field"] == "value")
>   |> filter(fn: (r) => r["id"] == "1")
>   |> filter(fn: (r) => r._value > 0)
>   |> first()
>   |> aggregateWindow(every: v.windowPeriod, fn: first, createEmpty: false)
>   |> yield(name: "first")

Nevertheless I would like to slightly criticize the design of grafana, in the point that “last” is offered in the reduce-transformation, but not “first”.
An occasional user like me does not necessarily want to additionally deal with influxdb, grafana is already complicated enough (next question will probably follow soon ).