HI all, I need help with a cumulativesum() - I’ve worked tirelessly at this and still can’t get it right. I’m new to flux and having a hard time. This query below outputs a table (also below). I need to do a runningsum of the “discharge cycles” column as a 4th column in this table. Thank you all for any help!

ts = from(bucket: “rdata”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “site”)
|> filter(fn: (r) => r[“point_name”] == “kW”)

ts1 = ts |> aggregateWindow(every: 1d, fn: sum)
ts2 = ts |> aggregateWindow(every: 1d, fn: sum)

join(tables: {s1: ts1, s2: ts2 }, on: [“_time”], method: “inner”)
|> map(fn: (r) => ({ r with dischargemwhs: if r._value_s1 > 0.0 then r._value_s1 / 3600000.0 else 0.0 }))
|> map(fn: (r) => ({ r with chargemwh: if r._value_s2 < 0.0 then r._value_s2 / -3600000.0 else 0.0 }))
|> map(fn: (r) => ({ r with discharge_cycles: r.dischargemwh / 15.0 }))
|> drop(columns: [“_value_s1”, “_value_s2”, “_field_s1”, “_field_s2”, “_measurement_s1”, “_measurement_s2”, “_start_s1”, “_start_s2”, “_stop_s1”, “_stop_s2”, “point_name_s1”, “point_name_s2”, “register_s1”, “register_s2”, “root_s1”, “root_s2”, “site_controller_id_s1”, “site_controller_id_s2”, “site_name_s1”, “site_name_s2”, “source_device_id_s1”, “source_device_id_s2”, “site_controller_s1”, “site_controller_s2”, “source_device_type_s1”, “source_device_type_s2”])

scott
February 7, 2024, 4:39pm
2
@Marion_Akagi I simplified your query a little bit and it should be much more performant. You don’t need a join and I condensed the mulitple `map()`

calls down to a single `map()`

call. In that, I explicitly map only the columns you want returned so it drops everything else. It also duplicates the `discharge_cycles`

columns as `discharge_cycles_total`

. I then run `cumulativeSum()`

on the `discharge_cycles_total`

column:

```
from(bucket: "rdata")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "site")
|> filter(fn: (r) => r["point_name"] == "kW")
|> aggregateWindow(every: 1d, fn: sum)
|> map(
fn: (r) => {
dischargemwhs = if r._value > 0.0 then r._value / 3600000.0 else 0.0
chargemwh = if r._value < 0.0 then r._value / (-3600000.0) else 0.0
discharge_cycles = dischargemwhs / 15.0
return {
time: r._time,
dischargemwhs: dischargemwhs,
chargemwh: chargemwh,
discharge_cycles: discharge_cycles,
discharge_cycles_total: discharge_cycles,
}
},
)
|> cumulativeSum(columns: ["discharge_cycles_total"])
```

Let me know if this solves your problem.

1 Like

This is AMAZING. Thank you so much! I’ll use this to modify some of my other queries to run faster. I appreciate you.

scott:

```
from(bucket: "rdata")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "site")
|> filter(fn: (r) => r["point_name"] == "kW")
|> aggregateWindow(every: 1d, fn: sum)
|> map(
fn: (r) => {
dischargemwhs = if r._value > 0.0 then r._value / 3600000.0 else 0.0
chargemwh = if r._value < 0.0 then r._value / (-3600000.0) else 0.0
discharge_cycles = dischargemwhs / 15.0
return {
time: r._time,
dischargemwhs: dischargemwhs,
chargemwh: chargemwh,
discharge_cycles: discharge_cycles,
discharge_cycles_total: discharge_cycles,
}
},
)
|> cumulativeSum(columns: ["discharge_cycles_total"])
```

@scott It’s bringing up a “unsupported input type for sum aggregate: string”. the “kw” value is a number. Do I need to cast or something?

I added a row after aggregateWindow to fix the data type error and now it works! Adding my solution here to help others.

```
|> map(fn:(r) => ({ r with kWFl: float(v: r.kW) }))
```