Sum in map function

Fluxlang
1

Hey,
i want to use the sum Function within the map() function but i only get the error:
image
At the end i want to divide the sum of two columns like this: sum(column1)/sum(column2)
My query at the moment looks like this:

from(bucket: “Bucket”)

|> range(start: -365d)

|> filter(fn: (r) =>

r._measurement =="Measurement" and 

r._field == "value1" or 

r._field == "Status" or 

r._field == "value2"

)

|> pivot(

rowKey:["_time"],

columnKey: ["_field"],

valueColumn: "_value"

)

|>keep(columns: ["_time", “value1”, “Status”,“month”,“year”,“value2”])

|>fill(column: “Status”, usePrevious: true)

|> filter(fn: (r) =>

r.Status ==1

)

|>drop(columns:[“Status”])

|>group(columns: [“month”, “year”])

|> map(fn: (r) => ({

  _time: r._time,

  "1/2": sum(column: "value1")/sum(column: "value2")

}))

Can anyone help me please?

2

@Patse sum() operates on a stream of tables, so you can’t use it inside of map(). Map iterates over and operates on each row as an individual record. What I’d suggest doing is creating separate streams of tables for each field (value1, value2, and Status) and window and aggregate those by month (so you don’t have to group by month and year). For value1 and value2, you apply the sum aggregate and for Status you can apply the last selector with the createEmpty sent to true if there are months with no Status values. You can then union those streams together, pivot, filter, and divide the sum of value1 by the sum of value2 per month where Status is equal to 1. It would look something like this:

data = from(bucket: "Bucket")
    |> range(start: -365d)
    |> filter(fn: (r) => r._measurement =="Measurement")

value1 = data |> filter(fn: (r) => r._field == "value1") |> aggregateWindow(every: 1mo, fn: sum)
value2 = data |> filter(fn: (r) => r._field == "value2") |> aggregateWindow(every: 1mo, fn: sum)
Status = data |> filter(fn: (r) => r._field == "Status") |> aggregateWindow(every: 1mo, fn: last, createEmpty: true) |> fill(usePrevious: true)

union(tables: [value1, value2, Status])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> keep(columns: ["_time", "value1", "value2", "Status"])
    |> filter(fn: (r) => r.Status == 1)
    |> drop(columns:["Status"])
    |> map(fn: (r) => ({_time: r._time, "1/2": r.value1/r.value2}))
1 Like