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?
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()
Unfortunately that doesn’t seem to work.
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")
Why is simple things like this so difficult with flux?
@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.
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?
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)}))
Thanks Grant and sorry for the later reply, I also discovered I can just comment the yield lines out.
