Calculate values in query

Fluxlang
1

Hi All,

I have the following query:

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Zurich")

currentvalues = from(bucket: "smarthome")
  |> range(start: date.sub(d: 1d, from: v.timeRangeStart), stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ElectricalEnergy")
  |> filter(fn: (r) => r["Scope"] == "House" or r["Scope"] == "HeatpumpCompressor" or r["Scope"] == "HeatpumpCtrl")
  |> filter(fn: (r) => r["Source"] == "SaiaBurgess")
  |> filter(fn: (r) => r["UpdateInterval"] == "Day")
  |> filter(fn: (r) => r["_field"] == "value")
  |> truncateTimeColumn(unit: 1m)
  |> map(fn: (r) => ({r with _time: date.sub(d: 1d, from: r._time)}))
  |> aggregateWindow(every: 1mo, fn: last, createEmpty: true, timeSrc: "_start")
  |> difference()
  |> yield()

So I query values of three different tags. The all have a value at the same point in time. For example for one month:

image
image1798×232 42 KB

I would like now to calculate for the same point in time:

House - Heatpumpcompressor - Heatpumpctrl

I tried different things with group() and sum() and difference() but did never really succeed. Any ideas?

Best regards,
Oliver

2

Already found a solution myself :slight_smile:

currentvalues = from(bucket: "smarthome")
  |> range(start: date.sub(d: 1d, from: v.timeRangeStart), stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ElectricalEnergy")
  |> filter(fn: (r) => r["Scope"] == "House" or r["Scope"] == "HeatpumpCompressor" or r["Scope"] == "HeatpumpCtrl")
  |> filter(fn: (r) => r["Source"] == "SaiaBurgess")
  |> filter(fn: (r) => r["UpdateInterval"] == "Day")
  |> filter(fn: (r) => r["_field"] == "value")
  |> truncateTimeColumn(unit: 1m)
  |> map(fn: (r) => ({r with _time: date.sub(d: 1d, from: r._time)}))
  |> aggregateWindow(every: 1mo, fn: last, createEmpty: true, timeSrc: "_start")
  |> difference()
  |> pivot(rowKey: ["_time"], columnKey: ["Scope"], valueColumn: "_value")
  |> map(fn: (r) => ({r with _value: (float(v: r.House) - float(v: r.HeatpumpCompressor) - float(v: r.HeatpumpCtrl))} ))
  |> keep(columns: ["_time", "_value"])
  |> yield()