aggreateWindow window period and offset, how to RIGHT-ALIGN?

InfluxDB 2
,
1

I am trying to understand the relationship between aggregateWindow’s window size and the offset parameter.

My goal is to get the sum of a value over the dashboard’s time span, AND the sum of the previous timespan of the same length.

Dashboard time span: Last-1-year

import "date"
timeInRange = duration(v: (int(v: v.timeRangeStop) - int(v: v.timeRangeStart)))
newStart = date.sub(from: v.timeRangeStart, d: timeInRange)

from(bucket: "DOWNSAMPLE")
  |> range(start: newStart, stop: v.timeRangeStop)
  // simplified code here 
  |> filter(fn: (r) => r["_field"] == "triggers")
  |> aggregateWindow(every: 365d, fn: sum, createEmpty: false)
  |> yield(name: "sum")

Which gives me this:
(displaying in time-graph here to understand what’s happening)

365_nooffset
365_nooffset817×240 13.5 KB

The last datapoint (2024-08-21) does not span a full year, only the duration to the previous datapoint (2023-12-18).

When I add an offset of 247d (found by trial and error) to the aggreagteWindow function, I get the datapoints about where I expect them to be.

|> aggregateWindow(every: 365d, fn: sum, createEmpty: false, offset: 247d)

365_offset
365_offset822×242 11.4 KB

I also tried setting the window period to “1y”, and then I had to set the offset to the day-of-the-year minus one, to get the alignment I wanted. Which makes sense with the 1-y window starting on the first day of the year.

todayDay = duration(v: (date.yearDay(t: now())-1) * 24 * 60 * 60 * 1000 * 1000 * 1000 )

|> aggregateWindow(every: 1y, fn: sum, createEmpty: false, offset: todayDay)

Instead of figuring out a dynamic offset for each dashboard time span duration, is there a way to start the window period at the LAST data point (going right to left)? I tried negative ‘period’ parameters, but without success.

InfluxDB 2.7.3
Graphs with Grafana 10.3.3

2

Hello @evsc,
I’m a little confused. Why

timeInRange = duration(v: (int(v: v.timeRangeStop) - int(v: v.timeRangeStart)))
newStart = date.sub(from: v.timeRangeStart, d: timeInRange)

That would contain both ranges right? Like 2 years?

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

? Right?
if timeRange stop was Friday and timeRangeStart is Monday
then timeInRange would be 5 days
and newstart = Thursay. So you’re querying for 10 days total. Or 2x the period. Is this what you want? It sounds like you want to query for 1 year and then the previous year starting from the left most point?

I’m confused by this as well:

The last datapoint (2024-08-21) does not span a full year, only the duration to the previous datapoint (2023-12-18).

Are you only looking to do this for 2 points?
If so I might not bother with aggwindow.

I might just do something like:

first_point = from(bucket: "DOWNSAMPLE")
  |> range(start: -1y)
  // simplified code here 
  |> filter(fn: (r) => r["_field"] == "triggers")
|> sum()
|> yield(name: "first point")

second_point = from(bucket: "DOWNSAMPLE")
  |> range(start: -2y, stop: -1y)
  // simplified code here 
  |> filter(fn: (r) => r["_field"] == "triggers")
|> sum()
|> yield(name: "second point")
3

Hello Anais, thanks for the quick response.

Yes, ultimately I want to only get 2 datapoints. 1 for the selected timespan, and 1 for the previous timespan.

Because I want to use the “show percentage change” feature of the stat-panel.
perc_change

I imagine I could query the two datapoints separately, but is there a way to then recombine them so that they work for that feature?
I just thought, there must be a way to also get them in one query only.