# Try to aggregate data up to yesterday evening WITHOUT today's value
**URL:** https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505
**Category:** Fluxlang
**Created:** [April 1, 2022, 4:57pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505 "2022-04-01T16:57:08Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@neuvidor](https://community.influxdata.com/u/neuvidor)
#### Post date: [April 1, 2022, 4:57pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/1 "2022-04-01T16:57:08Z")
Hello everybody,
I have some data stored each minute in a bucket (room temperatures). No issue with that.
I’m trying to display for one of this temperature the average of each previous weeks during the 2 previous month of the current day. The behavior will be moving weeks average, current day not included.
The issue I have is regarding the range selection, indeed, I did not succeed to delete the current day data causing bad chart.
- 
Below what I have already tried without success.
### Query tried #01 :
- You can see that I’m getting the current day data additionnaly to the previous aggregation which is not acceptable.
- 
- The corresponding query :
```auto
import "date"
import "experimental"
import "math"
DAY_STOP = today()
DAY_START = experimental.addDuration(d: -2mo, to: DAY_STOP)
from(bucket: "home1_daily_data")
|> range(start: DAY_START, stop: DAY_STOP)
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "energie")
|> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
|> filter(fn: (r) => r["instance"] == "test")
|> filter(fn: (r) => r["source"] == "calculated")
|> aggregateWindow(every: 1w, fn: mean)
```
### Query tried #02 :
- You can see that I’m getting 2 values for the same day which is also not acceptable
- 
- The corresponding query :
```auto
import "date"
import "experimental"
import "math"
CURRENT_DAY = today()
DAY_STOP = experimental.addDuration(d: -1s, to: CURRENT_DAY)
DAY_START = experimental.addDuration(d: -2mo, to: DAY_STOP)
from(bucket: "home1_daily_data")
|> range(start: DAY_START, stop: DAY_STOP)
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "energie")
|> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
|> filter(fn: (r) => r["instance"] == "test")
|> filter(fn: (r) => r["source"] == "calculated")
|> aggregateWindow(every: 1w, fn: mean)
```
### Expected result if we are the 01/04/2022 🙂
- 
---
### Author:  [@Anaisdg](https://community.influxdata.com/u/Anaisdg)
#### Post date: [April 1, 2022, 8:26pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/2 "2022-04-01T20:26:53Z")
Hello @neuvidor,
Are you just sharing your solution? Or do you still have a question? It looks like you got what you were looking for?
Although would think if you didn’t want to include data from today I would do:
```auto
start: my_start, stop: today()
```
And I would expect just that to work.
---
### Author:  [@neuvidor](https://community.influxdata.com/u/neuvidor)
#### Post date: [April 1, 2022, 9:18pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/3 "2022-04-01T21:18:02Z")
Hello @Anaisdg ,
I still have the question because your proposal does not work as expected.
### Your advised query :
```auto
from(bucket: "home1_daily_data")
|> range(start: -2mo, stop: today())
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "energie")
|> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
|> filter(fn: (r) => r["instance"] == "test")
|> filter(fn: (r) => r["source"] == "calculated")
|> aggregateWindow(every: 1w, fn: mean)
```
### Query’s result :
- Today’s data is still present which is not good for my chart
- 
---
### Author:  [@scott](https://community.influxdata.com/u/scott)
#### Post date: [April 5, 2022, 3:31pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/4 "2022-04-05T15:31:07Z")
@neuvidor `today()` is based on UTC time, so you may need to apply an offset to the timestamp returned from `today()` to account for your timezone. For example, if you’re in the US EDT timezone, you’d need to offset the timestamp by `-4h`. To do this, use [`experimental.addDuration()`](https://docs.influxdata.com/flux/v0.x/stdlib/experimental/addduration/):
```javascript
import "experimental"
from(bucket: "home1_daily_data")
|> range(start: -2mo, stop: experimental.addDuration(d: -4h, to: today()))
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "energie")
|> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
|> filter(fn: (r) => r["instance"] == "test")
|> filter(fn: (r) => r["source"] == "calculated")
|> aggregateWindow(every: 1w, fn: mean)
```
---
### Author:  [@neuvidor](https://community.influxdata.com/u/neuvidor)
#### Post date: [April 6, 2022, 3:52pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/5 "2022-04-06T15:52:04Z")
@scott ,
Thanks for your proposal even if I always use UTC format as you can see below.
Unfortunately your proposal has the same behavior of my previous examples. You can notice that the value in the purple circle is not OK because there is no 7 days compared to the previous value (see red mark).
Below the response of your querry proposal :
- 
---
### Author:  [@scott](https://community.influxdata.com/u/scott)
#### Post date: [April 6, 2022, 4:09pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/6 "2022-04-06T16:09:14Z")
Interesting. Ok, what are the last few rows of your data _before_ `aggregateWindow()`? To get them, use `tail()`:
```javascript
import "experimental"
from(bucket: "home1_daily_data")
|> range(start: -2mo, stop: experimental.addDuration(d: -4h, to: today()))
|> filter(fn: (r) => r["_measurement"] == "kWh")
|> filter(fn: (r) => r["_field"] == "value")
|> filter(fn: (r) => r["domain"] == "energie")
|> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
|> filter(fn: (r) => r["instance"] == "test")
|> filter(fn: (r) => r["source"] == "calculated")
|> tail(n: 10, offset: 0)
```
I’m curious to see what the timestamps on those last few rows are.
---
### Author:  [@neuvidor](https://community.influxdata.com/u/neuvidor)
#### Post date: [April 6, 2022, 4:41pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/7 "2022-04-06T16:41:15Z")
> [@scott](#):
>
> I’m curious to see what the timestamps on those last few rows are.
I can directly answer you, there is only 1 value per day stored. There is a mistake on my original description above, I confused with my “home1\_raw\_data” bucket wich have a value stored every minute but with only 30 days of retention.
Below what I got :
- 
---
### Author:  [@scott](https://community.influxdata.com/u/scott)
#### Post date: [April 6, 2022, 8:38pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/8 "2022-04-06T20:38:50Z")
I _think_ I know what’s going on. I think it’s because you’re querying by calendar month and aggregating by week. When you use `every: 1w`, Flux uses the Unix epoch as the starting point to increment and define weeks. The unix epoch started on a Thursday, so all `1w` windows represent Thursday to Wednesday. `aggregateWindow()`, by default, uses the `_stop` time of the window as the new `_time` value of the output aggregate point. So while the queried data only contains points from last month, one or more of those points falls in a `1w` window that overflows into the next month and you get a `_time` value from the next month.
Because you’re querying by calendar month, the length of the time range can vary and may not divide well by the `every` parameter. This _may_ leave a remainder of rows that will “overflow” into a window that gets assigned a timestamp outside of the range. I’m not 100% sure.
I think the first thing you should try is to aggregate every `7d` instead of one week, just to avoid the weirdness involved in with querying calendar weeks based on the Unix epoch. If that still results in this extra row, update the query time range to be a multiple of the `every` value. For example, `range(start: -63d)`.
This is only a guess, but it makes sense in my head 😄
---
### Author:  [@neuvidor](https://community.influxdata.com/u/neuvidor)
#### Post date: [April 15, 2022, 9:19pm UTC](https://community.influxdata.com/t/try-to-aggregate-data-up-to-yesterday-evening-without-todays-value/24505/9 "2022-04-15T21:19:57Z")
Hello @scott !
Your explanation is really interresting, thanks for that ! It seems that your are right regarding thursday, this is a key day for flux which is a bad news for me…
I tried your proposal but unfortunalety I got the same behavior.
## So the new question is :
Is there a way to aggregate some data starting from the “stop date” to the “start date”, like a moving average based on the last 7 days without taking in account the current day ?