Issue with using Transform to sum two flux queries

InfluxDB 2
,
1

Having an issue summing these two queries with the Grafana transform, wondering if there was a better way. Here are the queries:

from(bucket: “my-bucket”)
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) =>
r.resourceName == “AvgMotorCurrent1”
)
|> drop(columns: [“_start”, “_stop”])
|> map(fn: (r) => ({r with _value: ((r._value * float(v:720)) + float(v:208)) / float(v:1000)}))
|> sum()
|> map(fn: (r) => ({r with _value: r._value * ${cost}}))
|> last()

from(bucket: “my-bucket”)
|> range(start: v.timeRangeStart, stop:v.timeRangeStop)
|> filter(fn: (r) =>
r.resourceName == “AvgMotorCurrent2”
)
|> drop(columns: [“_start”, “_stop”])
|> map(fn: (r) => ({r with _value: ((r._value * float(v:720)) + float(v:208)) / float(v:1000)}))
|> sum()
|> map(fn: (r) => ({r with _value: r._value * ${cost}}))
|> last()

It returns the correct values I just need to add them together to get a sum. Any suggestions?

2

Hello @Ryebot33,
Yes!
I’m not sure why the grafana transform isnt working. But you could alternatively do something like this in flux:

// Define a function to apply the common transformations
applyTransformations = (table) => 
  table
    |> drop(columns: ["_start", "_stop"])
    |> map(fn: (r) => ({r with _value: ((r._value * float(v:720)) + float(v:208)) / float(v:1000)}))
    |> sum()
    |> map(fn: (r) => ({r with _value: r._value * ${cost}}))
    |> last()

// Fetch and transform data for AvgMotorCurrent1
current1 = from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.resourceName == "AvgMotorCurrent1")
  |> applyTransformations()

// Fetch and transform data for AvgMotorCurrent2
current2 = from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.resourceName == "AvgMotorCurrent2")
  |> applyTransformations()

// Combine the results and sum the _value fields
union(tables: [current1, current2])
  |> sum(column: "_value")
2 Likes
3

@Ryebot33 And here’s another alternative:

This method creates a function that returns the total sum of a specified resource as a scalar value. It then adds the sums of your two resources and includes them in an ad hoc table created with array.from().

import "array"

getResourceSum = (resource) =>
    (from(bucket: "my-bucket")
        |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
        |> filter(fn: (r) => r.resourceName == resource)
        |> map(fn: (r) => ({r with _value: ((r._value * 720.0) + 208.0) / 1000.0}))
        |> sum()
        |> map(fn: (r) => ({r with _value: r._value * ${cost}}))
        |> last()
        |> getColumn(fn: (key) => true, column: "_value"))[0]

totalResourceSum = getResourceSum(resource: "AvgMotorCurrent1") + getResourceSum(resource: "AvgMotorCurrent2")

array.from(
    rows: [
        {_time: v.timeRangeStop, _value: totalResourceSum},
    ]
)
2 Likes