How to graph a cumulative sum of different influxdb series

Grafana Dashboards
,
1

Hi all,

I am using grafana 10.4.1 as part of home assistant on a raspi 4 together with influxdb 1.8.10.

I want to have a cumulative sum in a graph of my savings from my solar panels. That is easy in my eyes as long a the kWh cost remains constant. I do have a metric with the sum of the self-used kWh and this can be multiplied by some constant kWh cost.

But now I do have 2 kWh cost changes in fast succession and I want that cumulative sum still to be correct.

So I introduced a new metric with the kWh cost to move away from that constant kWh cost.

Now I do see 2 approaches which both failed up to now:

  1. Create a grafana visualization with 3 series
    a) the kWh cost
    b) the daily sum of self used kWh
    c) a) and b) multiplied (using a math expression) for the daily savings

Now I do want another expression/transformation/whatever to build a cumulative sum (and graph that) on the results of c), but I can’t find a way to do that.

  1. Do all the math not in grafana but in influxdb. But I read that infuxql does not support joins of different series (e.g. how to join two measurement of influxdb - Stack Overflow) and all I tried failed. At least influxql has the cumulative_sum function to archieve similar stuff, but due to the lack of joins I have no better idea. Only fluxql does seem to have join support, but can’t be used from grafana, right? And is fluxql even capable of a cumulative_sum as well?

Any ideas on that matter? Thx.

2

can you show a table view of your data and queries?

3

Grafana based approach:

Query to get series of self used kWh per day:

SELECT max(“value”) - min(“value”) FROM “kWh” WHERE (“friendly_name”::tag = ‘Solar_selbst_verbraucht_Summe’) AND $timeFilter GROUP BY time(1d) fill(previous)

Query to get series for cost of kWh:

SELECT mean(“value”) FROM “EUR” WHERE (“friendly_name”::tag = ‘Strom_Preis_pro_kWh’) AND $timeFilter GROUP BY time(1d) fill(previous)

Expression to calc savings

$A * $B

visualization:

2024-04-04_12h47_28
2024-04-04_12h47_282462×500 54.9 KB

the white data (which is the result of the expression) is wanted to be summed up using a cumulative sum.

Table data:

2024-04-04_12h52_20
2024-04-04_12h52_201582×864 69.8 KB

unfortunatly with german column names, but the data from the 3rd column is expected to be summed up using a cumulative sum

Thx.

4

And here is one of my influxql ideas which did not work out:

select
    CUMULATIVE_SUM( SUM(self_used_kwh * cost_kwh) )
from
    (
    SELECT max("value") - min("value") as self_used_kwh FROM "kWh" WHERE ("friendly_name"::tag = 'Solar_selbst_verbraucht_Summe') AND $timeFilter
    ),
    (
    SELECT mean("value") as cost_kwh FROM "EUR" WHERE ("friendly_name"::tag = 'Strom_Preis_pro_kWh') AND $timeFilter
    )
GROUP BY time(1d) 
fill(previous)

Error is:
failed to execute query [C]: expected field argument in sum()

6

Sum seems to require one value but you have a multiplier in it

Might want to post in influxdb forum

7

Well … might be right. I will try it with the idea from your posted link.

But if there are any grafana based ideas I would indeed prefer them over any influxdb tweaks.

Thx.

1 Like