Grafana calculate the difference

revski · May 22, 2019, 3:46pm

Is it possible to calculate the difference of values of two queries A and B in singlestat in Graphane and output the result?
Zabbix v3.4, Grafana v4

danielgonzalez · May 23, 2019, 12:36am

You can do using Meta Queries Plugin

revski · May 23, 2019, 3:00am

Thx for your reply
https://grafana.com/plugins/goshposh-metaqueries-datasource this?
Can I use it for Singlestat to show result as a digit in the block?

revski · May 23, 2019, 10:51am

Installed, but did not help

danielgonzalez · May 23, 2019, 1:02pm

Yes, it!!

You are wrong, if your metric are name “metricA” and “metricB”, the arithmetic expression is:
´´B[‘metricB’]-A[‘metricA’]´´

revski · May 24, 2019, 1:31pm

unfortunately, the singlestat shows only last query

revski · June 7, 2019, 10:12am

I was able to solve this task. I used Blendstat panel, but I had to fix some functions. I added the “Diff” function to the file module.js.

agentsmith1612 · August 16, 2023, 6:22pm

Sorry that I used this topic for my purpose.

I am using InfluxDB 2.4 and want to calculate the different from a energy meter.

Short explanation:
I want to calculate the diffrentce of the meter reading from the day before at like 0:00 with the acutal meter reading.

Example:
Night 0:00 → 2365 kwh
actuall (20:16) → 2368 kwh
Diffrence = 3 kwh.

I used this query: from(bucket: “Stromzähler”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Einspeisung”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> yield(name: “last”)

And I used in the panel configuration Calculation Choose a reducer function / calculation – “Diffrence” and it gives me back exactly these difference.

But now I need from a second energy meter a difference to subtract this both diffrences.

How could I do that?

I found no way to calculate this in the query itself, because InfluxQL is not able to give you back a value at a exact time.

My idea was to store the value from this panel with the query in a varable and create a second panel with the second value, store it and use both in a third panel as a variable.

But how to? I don`t know how to do that and there are a lot of things in the documentation what I dont understand.
So I am here to ask for help.

grant2 · August 16, 2023, 7:08pm

Just to clarify the above statement, you have two queries, each with a unique difference value, that you want to perform a mathematical calculation on, correct?

I would use some of the tools in the Flux library…something like this:

query A:

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> yield(name: "queryA")

query B:

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> yield(name: "queryB")

Transformation:

agentsmith1612 · August 17, 2023, 7:57am

Hi Grant2,
thank you for your answer.
Let me explain a little bit more in details what I want to do.
My PV plant produces like 50 kwh every day and store this value in a field “daily yield”. Every morning when the inverter begins to produce energy the value is resettet to 0.
This energy is consumed by my house and the excess is going to the grid.
My two way energy meter measures in the second counter the excess energy that is feeded to the grid.
Now my daily saving has two parts:
First part is the fee that I get for the feeding to the grid. 8,2 Cent per kwh.
I am calculating this by using the difference in grafana and this query:

from(bucket: "Stromzähler")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({r with _value: r._value * ${kwhPrice_Einspeisung}}))
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

grafik

The second part is my own consumption:
To calculate this in gernal
daily yield - excess energy * my kwh price.

Third:
the whole saving per day is the sum of one and two.

My point is how to get the diffrence from first part in the panel for second part?
Because I used the difference in caluclation in grafana.

I try to do it in influx but did not get it working.

I do not see the difference in your shown queries? I need one query that shows the value of the night the day before to calculate the excact grid feeding amount.

grant2 · August 17, 2023, 9:02am

Instead of using the difference reducer function in Grafana (which calculates the difference between first and last value of a field), I am using in the aggregateWindow spread function, which returns the difference between the minimum and maximum values in a specified column. To be precise, spread calculates the difference between the minimum and maximum values (not first and last) but in an always-increasing series, the two are the same. My understanding from your initial post is that the you have an always-increasing series. Is that correct?

EDIT: I am also using the additional Flux features to define the day, so that we can find the spread for the day so far (i.e. from whatever time of day you are viewing the query LESS what the value was at 00:00).

agentsmith1612 · August 17, 2023, 9:49am

Hi grant2,

You are right. The energy counter for grid feeding is always increasing when the produced energy is higher as my consumption.
So I need the diffrence from yesterday night like 22 or 23 or 0 o clock, and now.
Because at night the feeding counter is not increasing.
Example:

How I could realize this calculation in my previouse post, to calculate the own consumption, the saving of own consumption and the overall savin?

grant2 · August 17, 2023, 11:21am

Let’s break this down into smaller pieces…

First, does this query give you the correct value for the energy used for today? It should give you the difference between the reading at 00:00 in your timezone and the reading at the time that you run the query.

(PS: The timezone that I have used below is Amsterdam, but change according to your needs)

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> yield(name: "queryA")

agentsmith1612 · August 17, 2023, 6:49pm

grant2:

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> yield(name: "queryA")

Hi grant,
Timezone Amsterdam is correct.

With you query I got the value if 16,05 kwh.
That is the correct amount between the counter yesterday (2731 kwh) and the counter today (2747 kwh)

So that is the correct query to read the feeded energy to the grid.

grant2 · August 17, 2023, 7:15pm

OK, great. Let’s modify it slightly to see if we can calculate the savings that you get for feeding to the grid (8,2 Cent per kwh). Does this query work?

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> map(fn: (r) => ({r with _value: r._value * ${kwhPrice_Einspeisung}}))
 |> yield(name: "MoneyReceivedForFeedingToTheGrid")

agentsmith1612 · August 17, 2023, 7:21pm

8,2 Cent/kwh is correct.

With your query I got an 500 Internal Server Error and the panel shows “no data”.

grant2 · August 17, 2023, 7:42pm

How about this?

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> map(fn: (r) => ({r with _value: r._value * 0.082}}))   // maybe you have to write this as 0,082 depending on the local settings
 |> yield(name: "MoneyReceivedForFeedingToTheGrid")

grant2 · August 17, 2023, 7:52pm

Not to get ahead of ourselves, but can you clarify some things about the above formula?

Can it be written as:

(daily yield - excess energy) * my kwh price.

What is the value for “my kwh price”? Is it static or changing?
Is Daily Yield the same as what we calculated above (“queryA”, i.e. 2747 - 2731)?
Is there a formula or way to describe in words what is the Excess Energy?

agentsmith1612 · August 18, 2023, 4:11am

grant2:

import "timezone"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "Stromzähler")
 |> range(start: today())
 |> filter(fn: (r) => r["_measurement"] == "Einspeisung")
 |> filter(fn: (r) => r["_field"] == "value")
 |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
 |> map(fn: (r) => ({r with _value: r._value * 0.082}}))   // maybe you have to write this as 0,082 depending on the local settings
 |> yield(name: "MoneyReceivedForFeedingToTheGrid")

Hi grant2, it gives back, No data too.

Here my answers:

yes, you can right it as this. It is correctly when it comes to the math rules. From the math point of view my general formular was wrong.
kwh prirce for feeding is 0,082 €/kwh and my consumtion price for pulling energy from the grid is 0,2807 €/kwh both not changing.
No, daily yield is comming from my solar inverter, and measures how much energy I am producing per day.
Lets do some formulars:
total production of energy per day = yield day
excess energy = fedding counter now - feeding counter last night
own consumption = yield day -(fedding counter now - feeding counter last night)
earing by feeding in the grid =0,082 €/kwh* (fedding counter now - feeding counter last night)
saving by own consumption = 0,2807€/kwh*(yield day -(fedding counter now - feeding counter last night))
total saving per day= saving by own consumption + earning by feeding

Some words to explain it a little bit more:
When you solar plant produces 5000 Watts for 10 hours, then you produces 50 kwh in total.
Most time at the day you cant consume this 5000 Watt at home for you own. Also in cases when you take on your washing mashine and dish washer. So the amount that you cant consume for you own will go into the grid and will counted in the energy meter as feeding in a second counter. I got this 8,2 cent per kwh for that.
Due to much higher prices of energy from the grid, it makes sense to consume as much as possible on my own. But at some point this is not possible because of my lack of a electric car and my lack of a home battery.
So to have statistics about my saving I want these calculations.
The second goal why I want to have this, is to use it as a base of a calculation if a home battery makes sense, Key word: return of invest.

To calculate these saving per hand, thats not my problem, it seems that it is not so easy to do it in influx or grafana?!

grant2 · August 18, 2023, 9:21am

I had a typo (extra } ) in the formula. Put this line in there and confirm it works:

  |> map(fn: (r) => ({r with _value: r._value * 0.082}))

How does one obtain the daily yield? Is it presently being captured / stored in InfluxDB? Do you have a query for it? It should be just one single value for the day (or at any given hour of the day when you run the query), correct?