Liniuum
December 29, 2022, 8:41pm
1
How do I get the difference between the first and last vaules between a time range?

from(bucket: “test”)
|> range(start: -1d)
|> filter(fn: (r) => r[“_measurement”] == “Energy”)
|> filter(fn: (r) => r[“_field”] == “Unrest_Totals”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

This returns 100 (ish) values, how do I get the difference between the first and last?

grant1
December 29, 2022, 9:19pm
2
Maybe this?

```
First = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_totals")
|> first()
|> yield(name: "First")
Last = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_Totals")
|> last()
|> yield(name: "Last")
union(tables: [First, Last])
|> difference()
```

1 Like

Liniuum
December 29, 2022, 9:22pm
4
Unfortunately that doesn’t seem to work.

grant1
December 29, 2022, 11:26pm
5
Can you elaborate on any error messages that are display, or share a screenshot?

Does a single query like this yield the correct result?

```
from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_totals")
|> first()
|> yield(name: "First")
```

Liniuum
December 30, 2022, 3:06pm
6

That’s the first attempt but it gives the wrong answer.

The second suggestion leads to no results.

Liniuum
December 31, 2022, 7:44pm
7
Why is simple things like this so difficult with flux?

1 Like

grant1
January 1, 2023, 12:40pm
8
@Liniuum Remember that this is a volunteer-based, community forum. Everyone here tries to help one another. The documentation, how-to videos, and examples for Flux are very rich and numerous, so there is a lot of information out there to read and apply to your own data.

In your case, my first proposed query (with the union statement at the end) did work for you (in that no error messages were displayed), but you said it displayed the wrong value (4175443.9).

Here is some test data that I currently have access to. You can see the temperature difference over the past 24 hours.

and using the same query that I originally proposed, you can see it correctly identified the first value (40.12), the last value (34.63), and then calculated the difference as -5 degrees:

Hi Brian, I am simply frustrated with flux after becoming proficient using influxQL.

f = from(bucket: “test”)
|> range(start: -1d,)
|> filter(fn: (r) => r[“_measurement”] == “Energy”)
|> filter(fn: (r) => r[“_field”] == “Unrest_Totals”)
|> first()
|> yield(name: “first”)

l = from(bucket: “test”)
|> range(start: -1d,)
|> filter(fn: (r) => r[“_measurement”] == “Energy”)
|> filter(fn: (r) => r[“_field”] == “Unrest_Totals”)
|> last()
|> yield(name: “last”)

union(tables: [f, l])
|> difference()

This works somewhat, however it doesn’t seem to take the difference in any particular order.

and then with a refresh the number is positive. How can I prevent this or take the absolute value?

In this example the last value will always be greater than the first as this data is an accumulation of energy used in watt-hours.

Thanks and Happy new year.

grant1
January 1, 2023, 7:01pm
10
Add this line to the very beginning:

`import "math"`

and this function to the very end (after the `difference()`

function):

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

```
First = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_totals")
|> first()
|> yield(name: "First")
Last = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_Totals")
|> last()
|> yield(name: "Last")
union(tables: [First, Last])
|> difference()
```

That works great! Thanks for your patience.

Is there a way to drop the tables “first” and “last” and keep the difference table?

grant1
January 2, 2023, 3:16am
12
To get rid of the First and Last tables, you would remove the `yield()`

functions. The complete Flux query would look like this:

```
import "math"
First = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_totals")
|> first()
Last = from(bucket: "test")
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == "Energy")
|> filter(fn: (r) => r["_field"] == "Unrest_Totals")
|> last()
union(tables: [First, Last])
|> difference()
|> map(fn: (r) => ({r with _value: math.abs(x: r._value)}))
```

2 Likes

Thanks Grant and sorry for the later reply, I also discovered I can just comment the yield lines out.