Subtract first value from all others in time series

Grafana Dashboards
1

Hi!

I have an InfluxDB v1 database with the total energy recorded from an energy meter:

Time          | Energy
--------------+---------
1767575980393 | 4913.283
1767575983475 | 4915.175
1767575983476 | 4915.175
1767576000025 | 4926.108
1767576060017 | 4965.001
1767576120018 | 5004.106
1767576180014 | 5043.841
1767576240013 | 5082.945
1767576300009 | 5122.259
1767576360006 | 5161.994
1767576420005 | 5201.098
1767576480002 | 5241.044
1767576539003 | 5279.517
1767576540003 | 5280.148
1767576599996 | 5319.462
1767576659995 | 5359.197
1767576719994 | 5398.512
1767576779991 | 5437.616
1767576839986 | 5477.561
1767576899985 | 5516.665
1767576960055 | 5556.610
1767577020047 | 5595.714

I would now like to plot this data as time series, BUT it should always start at 0, regardless of the time range chosen. Basically, get the first value (4913.283), and subtract it from the data, then plot this difference versus time.

This is not possible in InfluxQL, so I’m trying this in Grafana 12.1.1.

So far, I have two queries:

Query A: SELECT "Energy" FROM MyTable WHERE $timeFilter

Query B: SELECT first("Energy") FROM MyTable WHERE $timeFilter

A returns the time series above, and B just a single row with the correct first value.

But I fail to subtract B from A correctly. I tried to join both queries via a transformation, which results in a third column having the first value in the first row, and “undefined” in all other rows. As result, the difference is [0, “undefined”, “undefined”, “undefined”…]

I think I have to replace “undefined” values by tha last value or something like that, but I’m currently lost.

Any idea?

2

Use query B to create dashboard variable, so it will have first value of selected timerange and then use it (subtract) in the panel query A.

3

Thanks!

That works fine!