To calculate my monthly power consumption (importing) and production (exporting) I need to subtract the last value from the first one as these values are incrementing counters. Now I want to subtract the consumption from production so I can see in a month whether I am consuming or producing more. So far my query for January is:
data1 = from(bucket: "HomeData")
|> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
|> filter(fn: (r) => r["topic"] == "Home/Kaifa")
|> filter(fn: (r) => r["_field"] == "Kaifa_total_imported")
temp_earlier_number1 = data1
|> last()
|> set(key: "_field", value: "Imported")
temp_later_number1 = data1
|> first()
|> set(key: "_field", value: "Imported")
union(tables: [temp_later_number1, temp_earlier_number1])
|> difference()
|> yield(name: "Imported")
data2 = from(bucket: "HomeData")
|> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
|> filter(fn: (r) => r["topic"] == "Home/Kaifa")
|> filter(fn: (r) => r["_field"] == "Kaifa_total_exported")
temp_earlier_number2 = data2
|> last()
|> set(key: "_field", value: "Exported")
temp_later_number2 = data2
|> first()
|> set(key: "_field", value: "Exported")
union(tables: [temp_later_number2, temp_earlier_number2])
|> difference()
|> yield(name: "Exported")
This query will give me the January production (exporting) and consumption (importing).
Now, how can I subtract importing from exporting and get a single number to show as a single stat?
Thanks!