Calculating percent value over time range

[ruehuber]

• What Grafana version and what operating system are you using?
  V 9.3.2. on Raspberry Debian GNU/Linux 11 (bullseye)

• What are you trying to achieve?
  My influxdb database is logging toltalizer values for SolarEnergy and ConsumedEnergy (and a few more)

What I like to have is:

• The amount of energy produced, consumed within the selected time period
• the average percentage of the SolarEnergy / ConsumedEnergy
  over the whole selected time period.

• How are you trying to achieve it?
  I have a time series database (influxdb) with the following query

from(bucket: “Photovoltaik”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “SMAInverter” or r[“_measurement”] == “GridMeter”)
|> filter(fn: (r) => r[“_field”] == “SolarEnergy” or r[“_field”] == “ConsumedEnergy” or r[“_field”] == “ReturnedEnergy”)
|> filter(fn: (r) => r[“meas_value”] == “Energy”)
|> aggregateWindow(every: 1d, offset:-1s, fn: last, createEmpty: false)

By using the “Stat” visualization with the “Value options” Calculate Difference:

To get the percentage of SolarEnergy / ConsumedEnergy, I´ve implemented it with a transform:
Mode: Binary operation
Operation: SolarEnergy / TotalConsumption

• What happened?
  The energy consumption and production of the selected time range works well as expected.

But the percentage of SolarEnergy / ConsumedEnergy does not show the desired result. The transform calculates the percentage per day in a new column. The “value options” Calculate Difference, then gives the difference between the first and the last percentage value.

• What did you expect to happen?
  What I like to have is the SolarIndex = SolarEnergy / ConsumedEnergy over the full selected time range (average).

• Can you copy/paste the configuration(s) that you are having problems with?
  see above

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  No

• Did you follow any online instructions? If so, what is the URL?
  No

Since I alredy spend hours with this, I would be really happy if anyone can give an hint how to solve it.

[grant2]

Hi and welcome to the forum.

Rather than working with Grafana transformations, let’s move to Influx Data Explorer and write your query there and view the raw data output (switch toggle).

Then let’s try to create a map function to do the math you desire.

Can you paste the output (screenshot) of your query as rendered with raw data in Influx Data Explorer?

[ruehuber]

Here is the raw data of the query:

Many thanks for your support

[grant2]

What does the data look like (while still in Influx Data Explorer) when you add this line after the aggregateWindow?

|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

If the above works as planned, then you can do a map() function to perform the calculations.

[ruehuber]

Hi,
meanwhile I added the pivot as suggested with this result:

Unfortuantely two tables where generated.
I played arround to use the map function.
First of all I calculated the differences of the totalizer values per day and the summed it up.
This worked pretty fine.
Then I tried to calculated the remaining values with map(). But I did not manage to use values from both tables. Is there a way to do so? Maybe merge both tables?

Here is the result of may latest attempt:

I would appreciate any help

[grant2]

Yes, you can join two tables and then do the math calculation once they are in the same table.

This is from another forum post where I was helping someone, but it illustrates the point:

t1 = from(bucket: "Stromzähler")
|> range(start: -1m)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()
|> yield(name: "value_t1")

t2 = from(bucket: "Stromzähler")
|> range(start: day.start, stop: day.stop)
|> filter(fn: (r) => r["_measurement"] == "Einspeisung")
|> filter(fn: (r) => r["_field"] == "value")
|> last()
|> yield(name: "value_t2")

result = join(tables: {t1: t1, t2: t2}, on: ["_time"])
|> map(fn: (r) => ({_value: (r._value_t1 - r._value_t2)}))
|> yield(name: "t1_minus_t2")

[ruehuber]

Hi,
did try to implement the join function with 2 tables, but did not really work as expected.
But I can use the query without joining the tables and just use the difference and culumlativeSum. Based on this query I can easily do the math with Grafana transforms.

Thank you so much for your support, I was able to solve my problem.
Regards
Reinhard