24h aggregate with multiple values sum

Fluxlang
1

Hey,
I have working query:

from(bucket: “autogen”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“item”] == “Inverter_AC_Lifetime_Energy”)
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> timeShift(duration: -300m, columns: [“_time”])
|> difference()
|> yield()

which gives me (in Grafana) aggregated bars each for 24h. Great.

Then I want to do simillar thing, but I want to get aggregated bar for sum of values. So I’ve created this:

from(bucket: “autogen”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“item”] == “SDM_total_import_kWh” or r[“item”] == “SDM_total_export_kWh” or r[“item”] == “Inverter_AC_Lifetime_Energy”)
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> timeShift(duration: -300m, columns: [“_time”])
|> difference()
|> map(fn: (r) => ({ r with _consumption: r.Inverter_AC_Lifetime_Energy - r.SDM_total_export_kWh + r.SDM_total_import_kWh }))
|> yield()

but it gives me an error: runtime error @7:6-7:128: map: cannot compile @ 7:14-7:127: unsupported binary expression invalid - invalid

What is the right way to do it?
To have this math calculation working: r.Inverter_AC_Lifetime_Energy - r.SDM_total_export_kWh + r.SDM_total_import_kWh ?

Thanks!

2

Hello @bartwitkowski,
It looks like you’re going to need to change the shape of your data some.
I always like to use multiple yield() functions to view the results of my data at different points like print statements and limit my output to a couple of lines so its easier to understand how flux is transforming my data.

Based on the way you’re filtering it looks like youre calculating the difference for multiple tables in your reuslts stream–a table for each tag.
If you want to subtract the values for each tag you’ll need to 1) combine your data together in one table by ungrouping 2) pivot on your tag values so that you can perform that calculation.

from(bucket: “autogen”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“item”] == “SDM_total_import_kWh” or r[“item”] == “SDM_total_export_kWh” or r[“item”] == “Inverter_AC_Lifetime_Energy”)
|> aggregateWindow(every: 24h, fn: last, createEmpty: false)
|> timeShift(duration: -300m, columns: ["_time"])
|> difference()
|> group()
|> pivot(rowKey:["_time"], columnKey: [“item”], valueColumn: “_value”)
|> map(fn: (r) => ({ r with _consumption: r.Inverter_AC_Lifetime_Energy - r.SDM_total_export_kWh + r.SDM_total_import_kWh }))
|> yield()


Let me know if that's closer to what you're trying to do. 
It would be helpful if you could share a screenshot of the raw data view after the difference() function if that isn't what you're looking for. 

Thanks!
3

Hey @Anaisdg,
That’s it, works as expected!
Thank you!