# Flux Assistance - Divide Two Integrals

Hi, I’m trying to create a single stat grafana panel that shows a percentage; the backend is InfluxDB 2.1 and I’m using Flux. This percentage can be computed by diving these two queries’ results:

``````from(bucket: "metrics")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "energy_meters" and r["_field"] == "site_instant_power")
|> integral(unit: 1h)
|> group(columns: ["_field"])
``````

and

``````from(bucket: "metrics")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "energy_meters" and r["_field"] == "load_instant_power")
|> integral(unit: 1h)
|> group(columns: ["_field"])
``````

and then multiplying the quotient by 100.

My question is how can I weave these together into a single Flux query for this panel? Thanks.

Also, for reference, this is the InfluxQL query I used to use on v1.8:

``````SELECT (1-integral("site_instant_power", 1h)/integral("load_instant_power", 1h))*100 FROM "energy_meters" WHERE \$timeFilter fill(null)
``````

Make each call separately and assign the ensuing tables to variables. Join the tables, then map (if necessary) over the joined tables. Psuecode …

``````site_instant_power = from(bucket: "metrics")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> ...

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> ...

experimental.join(left: site_instant_power, right: load_instant_power, fn: (left, right) => ({ <whatever calculations here> }))
|> map(fn: (r) => ({ < whatever calculations here> }))
|> yield()

``````

This content may be helpful: Querying and Data Transformations | Time to Awesome

Thanks, following your suggestions I produced this solution which works in my panel:

``````energy = from(bucket: "metrics")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "energy_meters")

p_site = energy
|> filter(fn: (r) => r["_field"] == "site_instant_power")
|> integral(unit: 1h)

|> filter(fn: (r) => r["_field"] == "load_instant_power")
|> integral(unit: 1h)

|> map(fn: (r) => ({
r with
self_powered: (1.0 - r._value_p_site / r._value_p_load) * 100.0
})
)
|> keep(columns: ["self_powered"])
|> yield(name: "self_powered")
``````

Is usage of

• `join()` as finally shown by @iwvelando
• `experimental.join()` as shown by @rickspencer3 r

really the proper solution for arithmetic with fields of a single measurement ?

My understanding is that `pivot()` is best practices method, see

With `schema.fieldsAsCols()` doing field arithmetic becomes really easy and compact, as explained by @Anaisdg .

Beyond that, `join()` seems to have a performance problem, see
Poor performance for join(): cpu and memory grows quadratically with row count
and `experimental.join()` seems to have a stability problem, see
Tried experimental.join(): good performance, but also "panic: unknown type invalid"

In my experience, if you have multiple series with the same timestamps (typically by writing multiple fields in the same line of line protocol), then pivoting is, indeed much easier. But if the fields are written separately with different timestamps, then you need to normalize the times and join.

@wfjm perhaps you could provide a code sample to demonstrate the pivoting approach?

True, some form of aggregation is almost always required, either for the reason you mention, so simply to reduce the amount of data to what can be shown in the visualization (usually the number of pixel in the pane displayl).
A simple pattern I use is

``````from(bucket: "dca")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "TfcMonitor")
|> filter(fn: (r) => r["_field"] == "dtq_min" or r["_field"] == "dtq_50")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: true)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({ r with _value: r.dtq_50 - r.dtq_min }))
|> keep(columns: ["_time", "_value", "host", "oid"])
|> yield()
``````

That script

• selects two fields `dtq_min` and `dtq_50`
• aggregates with `mean`
• calculates the difference of the two fields
• and keeps results organized by two tag keys `host` and `oid`

Here `mean` is used as aggregator, but any other should work too.

The `Flux` above is equivalent to the `InfluxQL` (using Grafana macros)

``````SELECT mean("dtq_50")-mean("dtq_min")
FROM "TfcMonitor"
WHERE \$timeFilter
GROUP BY time(\$__interval), "host", "oid" fill(null)
``````

So even with `InfluxQL` that was easily possible.