Project by two or more years

hello everyone,
can anyone help fix this flux script used to project by two or more years using window function without aggregation?

from(bucket:“bucket-name”)
|> range(start:2002-03-04T03:00:01.000000000Z, stop:2003-03-04T07:00:00.000000000Z)
|> filter(fn: (r) => (r[“_measurement”] == “measurement-name” and r[“id”] == “64138fa4-ebd1-43da-a477-1969790daada”))
|> filter(fn: (r) => (r[“_field”] != “string_field”))
|> map(fn: (r) => ({ r with Year: date.year(t: r._time) }))
|> group(columns:[“Year”, “_field”])
|> window(every:2y)
|> toFloat()
|> set(key:“aggregation”, value:“None”)
|> map(fn: (r) => ({ r with None: r._value }))
|> drop(columns:[“tag1”, “tag2”])
|> map(fn: (r) => ({ r with alias: r.Year }))
|> pivot(rowKey:[“alias”, “Year”], columnKey:[“aggregation”, “_field”], valueColumn:“_value”)

I only need to retrieve the starting of each window period. when running it, I get the same result of one year no matter of the period used in the window function.

the script is dynamically generated based on user input, if the user specify any aggregation then aggregateWindow is used.

Thanks!

Hello @m0hammed,
I’m not entirely sure I understand what the problem is. It would be helpful if you could give an example of what your output is and what your desired output is.

I’m not sure why you’re doing the following:

|> map(fn: (r) => ({ r with Year: date.year(t: r._time) }))
|> group(columns:[“Year”, “_field”])

If you want to window on that column though you’ll want to specify it in the timeColumn parameter:

I also like to use multiple |>yield() statements and print statements to make sure that each line of flux is transforming my data as I expect.

If you’re only querying for between 2 years and you use every:2y then yah I’d epect to see the results from one year only–2002.
So keep in mind your range is important too.

It does feel like the map and group with year is redundant though.