cumulativeSum with limits

InfluxDB 2
,
1

Hi guys,
I’m struggling trying to get a solution to this: I have a bucket with istant current produced by a PV system and the istant power comsumption for the AC line. I would like to understand how much energy is produced in excess. To do so I did these queries:

solar = from(bucket: “openhab”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “SolarRadiation” )
|> aggregateWindow(every: 1h, fn: mean)
|> fill(usePrevious: true)
|> map(fn: (r) => ({r with _field: “Solar”}))
|> map(fn: (r) => ({r with _value: r._value * 0.2048 * .8 * float(v: 44) * 1.78 * 1.09}))

PHA = from(bucket: “openhab”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “PHA_Power” )
|> aggregateWindow(every: 1h, fn: mean)
|> fill(usePrevious: true)
|> map(fn: (r) => ({r with _field: “PHA_Power”, _value: r._value * -1.0}))

PHB = from(bucket: “openhab”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “PHB_Power” )
|> aggregateWindow(every: 1h, fn: mean)
|> fill(usePrevious: true)
|> map(fn: (r) => ({r with _field: “PHB_Power”, _value: r._value * -1.0}))

PHC = from(bucket: “openhab”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “PHC_Power” )
|> aggregateWindow(every: 1h, fn: mean)
|> fill(usePrevious: true)
|> map(fn: (r) => ({r with _field: “PHc_Power”, _value: r._value * -1.0}))

PH_AB = join(tables: {PHA: PHA, PHB: PHB}, on: [“_time”])
PH_ABC = join(tables: {PH_AB: PH_AB, PHC: PHC}, on: [“_time”])

join(tables: {solar: solar, PH_ABC: PH_ABC}, on: [“_time”])
|> map(fn: (r) => ({r with _value: r[“_value_solar”]-(r[“_value_PHA”]+r[“_value_PHB”]+r[“_value_PH_ABC”])}))
|> cumulativeSum()

PHA, PHB, and PHC are the power from the AC line meter (3 phase system), solar is the power produced by the solar panels.

What I need is a sort of cumulativeSum that return 0 if the value is <= 0 or the value in other case.

Does anyone has any idea?

2

Hi @Alessio_Galliazzo

Maybe add this additional map() function in your last join table?

join(tables: {solar: solar, PH_ABC: PH_ABC}, on: [“_time”])
|> map(fn: (r) => ({r with _value: r[“_value_solar” - (r[“_value_PHA”]+r[“_value_PHB”]+r[“_value_PH_ABC”])}))
|> map(fn: (r) => ({
      _value:
        if r._value <= 0 then
          0
        else
          r._value
     }))
|> cumulativeSum()

I cannot verify the syntax of the above, so if an error is thrown, just try to figure out what might be missing.

3

Hi @grant1,
Thanks for your reply but unfortunatley it is not working, I mean, the query works perfectly as expected but it is not what I need.
The query you proposed cut values below 0, not the cumulativeSum below 0.
The goal that I have is to increase the cumulative sum of _value in any case but to decrease it only if the cumulative sum is greater than 0.
This is the tipical behaivour of a energy storage: The energy is increased if the input-output is greater then 0, and decreased if input-output is less then 0 but only if there is some energy in it (so down to 0). My energy level is the cumulativeSum, my I-O is the expression r[“_value_solar”]-(r[“_value_PHA”]+r[“_value_PHB”]+r[“_value_PH_ABC”])

Hope this better clarify my problem.

Thanks again for your time!