Combine two measurements

1

Hello all,

I’m trying to combine two measurements of a KwH meter with Influxdb 2. Something that should be simple, but I can’t figure it out. This is my query:

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_1" or r["entity_id"] == "energy_consumption_tarif_2")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: 24h, fn: max, createEmpty: false)
  |> difference()

This shows me a graph with “energy_consumption_tarif_1” and “energy_consumption_tarif_2”.

I’d like to combine this data. This meter has a reading for T1 and T2, I’d like to accumulate this.

image
image2292×1115 221 KB

For example:

  • energy_consumption_tarif_1 has a value of 60
  • energy_consumption_tarif_2 has a value of 40

I’d like to combine these two so it shows a total of 100. I tried messing with “group” but couldn’t get it to work. Any insight?

2

Hello @reneflux,
Are you wanting to sum the values of both fields at each timestamp?
If so, can you please try:

|> group(columns: ["_time"],  mode:"by")
|> sum()

Additionally, are you writing push data (is it being written at regular intervals)? Why are you using aggregateWindow? I have a suspicion that your aggregateWindow() function might be redundant if you’re just trying to return the raw data, calculate the difference between subsequent values, and sum across fields.

Please let me know if this is helpful. If it’s not, can you please export some of your data to annotated csv through the UI and share it with me?

thx :slight_smile:

3

Hello @Anaisdg ,

Thanks for the quick reply. Here’s a CSV, that might be easier.

kwh-meters-influxdb2.csv.gz (780 Bytes)

These are two kWh meters and they report the total kWh usage. I aggregate this data so I can see the total kWh usage per 24 hours :slight_smile:

4

Hello @reneflux,
You could do

from(bucket: "new")
  |> range(start: 2021-02-01T13:00:00.000Z, stop: 2021-02-02T21:00:00.000Z)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> aggregateWindow(every: 24h, fn: mean)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()

Or you could try:

import "influxdata/influxdb/schema"
from(bucket: "new")
  |> range(start: 2021-02-01T13:00:00.000Z, stop: 2021-02-02T21:00:00.000Z)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> aggregateWindow(every: 24h, fn: mean)
  |> group()
   |> pivot(
      rowKey:["_time"],
      columnKey: ["entity_id"],
      valueColumn: "_value"
    )
  |> map(fn: (r) => ({ r with _value: r.eca380 + r.em340 }))

Does that help?
Thanks for your patience

5

Thank you @Anaisdg ,

Unfortunately, I get this error for both:

unsupported input type for mean aggregate: string