How to calculate with the last value

Skyfallgamer · September 28, 2023, 5:28am

Hello Grafana Community

I have an inverter at home whose data I write into an InfluxDB and insert into grafana via the flux language.

I would like to multiply the daily yield of the inverter with the electricity price, however, I know the price only when I get the calculation.

This is the code for the daily energy:

import “date”

month = date.truncate(t: now(), unit: 1mo)

The days on which the electricity price changes I have entered manually via the line protocol.

is it somehow possible to multiply the daily yield with the last value of the electricity price?
e.g. multiply yesterday’s yield with the value from 1.1. since that was the last change in the electricity price.

Now it looks like this:

Thank you for any advice.

grant2 · September 28, 2023, 9:44am

Welcome @Skyfallgamer

This is more of a Flux question than a Grafana question, so can you switch over to Influx Data Explorer and post the two queries and their output? (I presume the electricity price is being stored in a different measurement…I think it should, and should be easy to do if you are sending it into InfluxDB using line protocol)

import "date"

month = date.truncate(t: now(), unit: 1mo)

yield = from(bucket: "inverter")
|> range(start: month)
|> filter(fn: (r) => r["_measurement"] == "dailyenergy")
|> filter(fn: (r) => r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start")
|> yield(name: "last")

and separately

price = from(bucket: "inverter") // or whatever your bucket name is that stores the price
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "price") // or whatever you call it
  |> filter(fn: (r) => r["..  // add your filters here
  |> filter(fn: (r) => r["...
  |> last()

Skyfallgamer · September 28, 2023, 12:16pm

Thank you for your quick feedback

Yes the electricity prices are in a separate bucket. (it’s German )
Below are the screenshots of the two querys.

1st query works:

import "date"

month = date.truncate(t: now(), unit: 1mo)

yield = from(bucket: "wechselrichter")
  |> range(start: month)
  |> filter(fn: (r) => r["_measurement"] == "tagesenergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start")
  |> yield(name: "last")

The 2nd query occurs an error if in the 1st line “price =” is written.

price = from(bucket: "strompreis")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "verkauf")
  |> filter(fn: (r) => r["_field"] == "abnahmetarif")
  |> last()

Without that it finds only entries if the range stretches over an area in which an entry is. In my case on 1st march with the value 0,18€.

from(bucket: "strompreis")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "verkauf")
  |> filter(fn: (r) => r["_field"] == "abnahmetarif")
  |> last()

I hope it is understandable

grant2 · September 28, 2023, 12:47pm

I think the non-working query just needs to have a yield statement, like this. Can you confirm this works? Then we can proceed to try to do a join or union or something to accomplish what you want to do.

price = from(bucket: "strompreis")
  |> range(start: -2y) // I assume the latest update to price would happen in the past 2 years
  |> filter(fn: (r) => r["_measurement"] == "verkauf")
  |> filter(fn: (r) => r["_field"] == "abnahmetarif")
  |> last()
  |> yield(name: "price")

Skyfallgamer · September 28, 2023, 2:01pm

Ok this query works.

If I am understanding this it always tell me the last value of the last 2 years. But how can i now link the values so that the values at the time of day are multiplied by the last price?
e.g. the daily yield of 28th February multiplied by the price of 1st January and the daily yield of 1st March multiplied by the price of 1st March.

grant2 · September 28, 2023, 3:49pm

Hi again @Skyfallgamer

Try this:

import "date"

month = date.truncate(t: now(), unit: 1mo)

price = from(bucket: "strompreis")
  |> range(start: -2y)
  |> filter(fn: (r) => r["_measurement"] == "verkauf")
  |> filter(fn: (r) => r["_field"] == "abnahmetarif")
  |> last()
  |> findRecord(fn: (key) => key._measurement == "verkauf", idx: 0)

yield = from(bucket: "wechselrichter")
  |> range(start: month)
  |> filter(fn: (r) => r["_measurement"] == "tagesenergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start")
  |> map(fn: (r) => ({ r with DailyAmount: r._value * price._value}))
  |> yield(name: "last")

With the above, you should see a new column called DailyAmount that should be the daily usage x the price

Skyfallgamer · September 28, 2023, 5:53pm

Hi @grant2

Thank you very much the query works!

Unfortunately, the query becomes more complicated when I want to apply it to my smartmeter. Thereby the sum of the following values:

from(bucket: "smartmeter")
  |> filter(fn: (r) => r._measurement == "total-feeding-phase1" or r._measurement == "total-feeding-phase2" or r._measurement == "total-feeding-phase3")
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()

must be subtracted with the sum of these values: bucket=“smartmeter”

from(bucket: "smartmeter")
  |> filter(fn: (r) => r["_measurement"] == "total-consumption-phase1" or r["_measurement"] == "total-consumption-phase2" or r["_measurement"] == "total-consumption-phase3")               
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()

I also need the difference of 1day because they are the total values.
Then the result should be multiplied by the price as before.

price = from(bucket: "electricity-price")
  |> range(start: -2y)
  |> filter(fn: (r) => r["_measurement"] == "sale")
  |> filter(fn: (r) => r["_field"] == "purchase rate")
  |> last()
  |> findRecord(fn: (key) => key._measurement == "sale", idx: 0)

Is it better to calculate the result using a long flux query or to create a separate query for the price and then calculate the rest in Grafana?

How would the query which is more efficent look like?

Thanks an advance!

grant2 · September 28, 2023, 6:09pm

I prefer to do as much of the mathematical computations in the query (whether SQL or InfluxDB or whatever), and then have Grafana do the visualization and/or alerting.

I am tied up for a bit, but will try to revisit this later.

grant2 · September 28, 2023, 11:16pm

Am I correct to assume you are trying to do this? (my syntax is not correct…this is just for you to confirm what you are trying to do). Note that I have added range and sum functions to each query.

aa = from(bucket: "smartmeter")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "total-feeding-phase1" or r._measurement == "total-feeding-phase2" or r._measurement == "total-feeding-phase3")
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference()
  |> sum()

bb=from(bucket: "smartmeter")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "total-consumption-phase1" or r["_measurement"] == "total-consumption-phase2" or r["_measurement"] == "total-consumption-phase3")               
  |> filter(fn: (r) => r["_field"] == "value")
  |> difference() 
  |> sum()

cc = aa - bb

Also, I did not forget the second part of your question…We’ll tackle that once we get the above straightened.

Skyfallgamer · September 29, 2023, 6:01am

It’s getting very complicated now I think

It tells me that a yield function is missing and I can not write “yield(cc)” or “yield(name: last)”.
I also don’t understand the calculation “cc = aa + bb” this would be to easy for Flux

Is there a possibility to create a query which takes the last price for every day since there is a new one? Because I already have all values I need in a table in Grafana just the price is missing.

Here are some pictures how it looks like now:

Here you see:

DAY | ELECTRICITY CONSUMED | ELECTRICITY SOLD | CONSUMED PRICE | SOLD PRICE | DIFFERENCE CONSUMED/SOLD PRICE

The 6 querys for consumed (1 query for each phase) and soled (1 query for each phase) electricity look like this:

from(bucket: "smartmeter")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "gesamteinspeisung-phase2")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: last, timeSrc: "_start")  
  |> difference()
  |> yield(name: "last")

Then I am calculating the 3 values to a sum:

And then I multiplicate the sum with a price I write down manually which could be realistic:

Last step was to get the difference of $I and $J

In the Query Options I also added a relative time to see all days in the current month

Yes I know that it would be better to do all the calculations in Flux but I am not a Flux expert and I find Grafana very beginner friendly and easy to use which is why I do more in Grafana than with the Flux language.

I know it’s a lot but I want to give you an overview of what my goal would be. Maybe you can tell me here what would be the easiest way for a Flux amateur.

Thanks in Advance!

grant2 · September 29, 2023, 9:54am

Hi @Skyfallgamer

The queries and formula (cc = aa - bb) were just to make clear what I thought you were trying to do, but it would not work in Influx (that’s why I said “my syntax is not correct”). Anyway…

Your original question was just about how to combine price (in measurement “verkauf”) with the yield (in measurement “tagesenergie”), and we solved that earlier. The table and formulas (in Grafana) that you showed all seem to work well, so what else remains?

I did want to mention the spread function, which is basically doing last() - first() . Since you have a meter that is always accumulating (right?), and you only care about each day, I believe you could get your daily data for A+B+C as follows:

  |> aggregateWindow(every: 1d, fn:spread) // this computes the spread between the first and last value of each phase over each day.  If you stopped your function here, you would get 3 values (one for each phase) for each calendar day
  |> aggregateWindow(every: 1d, fn:sum) // this would sum the 3 amounts in each calendar day, i.e. it would do what you are doing in expression G

Maybe try out the above in Influx Data Explorer first.

Skyfallgamer · September 29, 2023, 5:02pm

Hi @grant2

I tried again the query before but it doesn’t work as I wanted

import "date"

month = date.truncate(t: now(), unit: 1mo)

price = from(bucket: "strompreis")
  |> range(start: -2y)
  |> filter(fn: (r) => r["_measurement"] == "verkauf")
  |> filter(fn: (r) => r["_field"] == "abnahmetarif")
  |> last()
  |> findRecord(fn: (key) => key._measurement == "verkauf", idx: 0)

yield = from(bucket: "wechselrichter")
  |> range(start: month)
  |> filter(fn: (r) => r["_measurement"] == "tagesenergie")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false, timeSrc: "_start")
  |> map(fn: (r) => ({ r with DailyAmount: r._value * price._value}))
  |> yield(name: "last")

All daily values are multiplied by the last value of the price. I will try to make a script which produces a list for every day and the daily price of the current and then I will easily multiplicate this in Grafana.
The negative thing is that only the electricity price of the previous year is displayed and the column for the current year remains empty. Furthermore, it requires some human effort.

If there isn’t a easier way I would say thanks for your time and have a nice day

Topic		Replies	Views
Include InfluxDB prices in electricity consumption InfluxDB	0	241	April 23, 2023
Calculate value from previous row value, which is calculated as well InfluxDB	0	643	September 17, 2022
InfluxDB2 Power Consumption Group by Month ?!? InfluxDB	16	4591	February 23, 2024
New to Influxdb Grafana query InfluxDB influxdb , query-help	24	1982	March 10, 2023
Where is my last value! InfluxDB	1	2208	February 11, 2023

How to calculate with the last value

Related topics