Sum up only positive values of expression in time series

Grafana Dashboards
,
1

  • What Grafana version and what operating system are you using?
    Grafana v9.5.2 (cfcea75916) on Raspbian

  • What are you trying to achieve?
    I want to sum up a difference between two series, but only the positive values. Background is that I have a energy consumption and a energy production time series, both in InfluxDB. I want to calculate how much I get from the grid (consumption - production, but only positive values).

  • How are you trying to achieve it?
    I created two queries in a dashboard to show the production and the consumption in two lines. I created an expression to calculate the difference. Problem is that both negative and positive values are shown. I want to sum up only the positive values of that expression

Here’s the query for the consumption (A)

SELECT mean("value") FROM "autogen"."sensor.stromzahler_power_curr" WHERE $timeFilter GROUP BY time($__interval) fill(linear)

Production (B)

SELECT mean("value") FROM "autogen"."sensor.power_production_now" WHERE $timeFilter GROUP BY time($__interval) fill(previous)

The difference is created as a Math expression simple as

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

Welcome

Are you using influxql or flux query language? Could you post your query here?

1 Like
3

I’m using the standard editor. The first query looks like this, the second analogue:

SELECT mean("value") FROM "autogen"."sensor.stromzahler_power_curr" WHERE $timeFilter GROUP BY time($__interval) fill(linear)

The expression is just a math expression

$A - $B
4

So that is influxQL query language.

1 Like
5

Where is the query for $A or $B not sure which one yoir query refers to

6

It’s no query but just an expression. $A is the consumption, $B is the production

Screenshot_20230529-203801
Screenshot_20230529-2038011079Ă—529 45.5 KB

7

Yes but those expressions comes from queries right? please show us what the source query of those expressions are?

8

Here are the two queries:

$A

SELECT mean("value") 
  FROM "autogen"."sensor.stromzahler_power_curr" 
WHERE $timeFilter 
 GROUP BY time($__interval) fill(linear)

$B

SELECT mean("value") 
  FROM "autogen"."sensor.power_production_now" 
WHERE $timeFilter 
GROUP BY time($__interval) fill(previous)
9

Is there a possibility to generate this via the InfluxQL?

Something like this (pseudo SQL):

select max(0, ("autogen"."sensor.stromzahler_power_curr" -
  "autogen"."sensor.power_production_now" ) from ....

?
10

Hi @guerda

How about this?

$A

SELECT mean("value") 
  FROM "autogen"."sensor.stromzahler_power_curr" 
WHERE $timeFilter AND "value" > 0
 GROUP BY time($__interval) fill(linear)

$B

SELECT mean("value") 
  FROM "autogen"."sensor.power_production_now" 
WHERE $timeFilter AND "value" > 0
GROUP BY time($__interval) fill(previous)
11

The single values are always greater zero, only the difference can be negative.
I understand your queries that it would only filter out the series individual values which are 0 or lower. Am I getting this right?

12

just add a transformation at the end, filter by value.

image
image1446Ă—371 17.7 KB

2 Likes
13

:arrow_up: :arrow_up: :arrow_up: what he said :slightly_smiling_face:

14

this is why I do not like using grafana for something that should be done on the server side. grafana is presentation layer. or something like this

declare @power_production_now table(value int)

insert into @power_production_now
select 4 union
select 6

declare @stromzahler_power_curr table(value int)
insert into @stromzahler_power_curr
select 4 union
select 6

---this part below ↓↓↓↓↓↓↓↓↓↓, is it doable in influxql?
select p.value - c.value
 from @power_production_now p, @stromzahler_power_curr c
 where p.value - c.value > 0
1 Like
15

I mean, I agree on doing it in the query itself. I dont know how to use flux QL, but it is certainly doable.

however, in my experience if the user is not experienced enough on programming, Grafana side is easier to use.

I usually do some complex stuff in Flux, and left the filters that may need to be tweaked by other colleagues on Grafana side.

1 Like
16

agreed, also would be nice if OP migrated to flux. you can do much more cool stuff.

17

Thank you for all your input! I agree that it would be better to do on the backend. That’s why I’d like to do that in the query instead with visual filters.

What do you mean with “migrate to flux”? Is flux a complete different storage or a different query language?

18

When I tried this, this filtered also the other series so that everytime the “Netzbezug” is below 0, also power consumption and production where set to 0.
Is it possible to only apply the filter on the expression, instead of both series?

19

This may help explain the differences:

2 Likes
20

I got it working with the help of @fercasjr’s hint. Thank you all for your help!

image
image1298Ă—386 25.1 KB

Key was to only apply the transformation to the “Netzbezug”.

1 Like