Difference alternates between positive and negative values

InfluxDB 2
1

I created the following queries to show me the difference between yesterday and today. However, the result keeps changing between a positive and negative value. The value itself remains the same, only the sign changes. I suspect that the order in union is not fixed and that is where this comes from, but I could be wrong. Does anyone have an idea how to fix the problem?

import "date"

yesterday = from(bucket: "solar")
  |> range(start: date.sub(from: v.timeRangeStart, d: 1d), stop: date.sub(from: v.timeRangeStop, d: 1d))
  |> filter(fn: (r) => r._measurement == "mqtt_consumer")
  |> filter(fn: (r) => r._field == "yieldday")
  |> filter(fn: (r) => r.channel == "0")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()
  |> last()

today = from(bucket: "solar")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "mqtt_consumer")
  |> filter(fn: (r) => r._field == "yieldday")
  |> filter(fn: (r) => r.channel == "0")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()
  |> last()

union(tables: [yesterday, today])
|> difference()
|> map(fn: (r) => ({r with _value: r._value}))

Results in e.g. 500 in the first request and -500 in the next.

2

Welcome @BloodhunterD to the InfluxDB forum.

If the negative value you are receiving is correct, but the sign is wrong, you could use the absolute value function, e.g. the last line would become:

|> map(fn: (r) => ({r with _value: math.abs(x: r._value)}))

Also, are you sure the ‘today’ start and stop times are correct?

3

Hello and thank you @grant1 for your fast reply.

The value can be negative, so math.abs unfortunately does not fix the problem.

I use the query in Grafana, so I took the approach of using the selected period for today and the same period minus 1 day for yesterday, so I compare the latest values in each of these periods.

Update: One more thing I noticed is that the value also changes between negative and positive when it does not change, so no new values are added.

4

Hi @BloodhunterD

Thanks for the explanation re: the abs value and the explanation of the range for today. Agree nothing to change there.

Since you are using Grafana, why not use just these 2 queries (no union query)…

query A (which represents yesterday)

from(bucket: "solar")
  |> range(start: date.sub(from: v.timeRangeStart, d: 1d), stop: date.sub(from: v.timeRangeStop, d: 1d))
  |> filter(fn: (r) => r._measurement == "mqtt_consumer")
  |> filter(fn: (r) => r._field == "yieldday")
  |> filter(fn: (r) => r.channel == "0")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()
  |> last()

query B (which represents today):

from(bucket: "solar")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "mqtt_consumer")
  |> filter(fn: (r) => r._field == "yieldday")
  |> filter(fn: (r) => r.channel == "0")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()
  |> last()

…then in Grafana, do this:

image
image950×611 56.7 KB

5

I implemented it this way, but the result is NaN. Even though I don’t know why you have today and yesterday in the screenshot, I only have Value 1 and Value 2, the two values (unsigned integer) are correct in the output.

image
image911×539 32.9 KB

The time range has no effect on the result, no matter what I select it remains at NaN.

6

In order for the Binary Operation transformation to work, you will need to make Value 1 and Value 2 numbers using separate transformations BEFORE the binary transformation. Something like this:

image

7

If I do a conversion transformation before the binary operation transformation, then I can’t select the fields, but I noticed that when I switch to table view, I logically have two timestamps, each with Value 1 and Value 2 and two empty entries. Maybe it tries to calculate with the empty values?

image
image1592×602 47.2 KB

8

I have found a solution. If I apply the transformation concatenate fields before, both values are written in one row, so the calculation works now. I don’t know if it is the best solution, but it is one that works as of now.

image
image1575×883 37.9 KB

1 Like