Subtraction of the first and last values in the same time series and calculation using the display time

Grafana InfluxDB
,
1

Hello everyone,
I still haven’t found an answer to my question on your community and that’s why I’m turning to you all.
I use grafana 10 and influxDB.
For a given time series query, I would like to display the first and last values on the time window being viewed, then subtract them from each other.
Then I’d like to be able to manipulate that value, like being able to divide it by the polling time (in hours).

Using the stat panel, I tried to

  • make two queries, one first and one last
  • to carry out a single query and use the calculation function to display the difference and make a transformation with the time, but it’s not working.

Thank you all for your feedback
Alex

2

Welcome @alexvasseur

Are you using Flux or InfluxQL?

3

I don’t know.
I’m sharing my request screen with you.
This may speak to you a little more

Calculation
Calculation1911×927 162 KB

4

Hi @alexvasseur

You are using InfluxQL. To do math between two queries, you can use an expression or a transformation. Expressions are usually $A+$B, but then you can do $C/100. From your screenshot, it looks like you tried a transformation.

Also, sometimes Grafana (or InfluxDB) require that the timestamps be aligned when trying to add 2 values. Not sure if this is the case with your data. Maybe you can use the reducer function “Last” to get them both to be at the same timestamp?

5

Thanks for your reply.
I’m used to doing calculations using expressions by aligning the times of the series using group by: time($interval).

However, I can’t do certain calculations, hence my initial question.

I would like to perform the following type of calculation on the observed time window:

A: request from sensor A select: field(raw value) + first
B: request from sensor A select: field(raw value) + last

expression : $A - $B
==> for a visual stat it outputs NaN whatever the calculation selected in the value option menu

then I’d like to be able to manipulate the result of the expression, for example :
expression / time window viewed with a unit display in l/h or similar but with a time in hours.

6

What do you get with min max as a subquery them subtract these on the top query

Select maxVal - minVal as kaplow, 
maxVal, 
minVal
From (
Select max(value) as maxVal,
Min(value) minVal
From BoomShaka
Group by time
) zimzam

But in order for us to help you further it would be if you could provide inline csv data as follows as an example

device,current,timestamp
Cell 1,600,2023-08-29 04:04:19.539000
Cell 2,800,2023-08-29 04:04:29.779000
Cell 3,800,2023-08-29 04:04:29.779000

This way we will have your data in our influxdb. Otherwise it will just be guessing and going back and forth