Count only negative values

[ChrisChros]

Hello,
I’ve got an InfluxDB 2.5 database with measurements of Grid power usage. The Grid power is positive when our Solar PV is not enough to power up the house and we are importing from the grid. The value is negative when we are feeding the grid.

Now I would like to calculate on daily bases only the negative amount of Power when we are feeding the grid
I do already a similar calculation with the PV earnings, but there are the values only positive:

from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "PV-Leistung")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)

Dont know if this calculation is valid.
Next I would like to calculate (perhaps using integral() ) the cost of power exported, separately from the cost of power imported, because there are different rates

How would it look like if I want only count the negative values?

[grant1]

Have you tried this (inserted after the aggregateWindow function)?

|> filter(fn: (r) => r._value < 0)

[ChrisChros]

Yes, then I receive “no data” as result.

[Jay_Clifford]

Hi @ChrisChros,
The filter should go before the spread aggregation. The spread will return a positive number since its the difference between the max and min: spread() function | Flux 0.x Documentation

[grant1]

Thank you @Jay_Clifford for pointing that out. I needed another cup of coffee before I wrote that!

[ChrisChros]

Thanks for the hint, now its working.

[blitz]

Hi,

would you share the working query please?

Thx
blitz

[ChrisChros]

SELECT cumulative_sum(integral(“value”)) / 3600 FROM “default”.“modbus.0.holdingRegisters.40074_Netz_Leistung” WHERE (“value” > 0) AND $timeFilter GROUP BY time(1m) fill(null)