# Influxdb2: Combining last + sum to get weekly values **URL:** \ **Category:** InfluxDB\ **Tags:** influxdb, dashboard, query-help\ **Created:** [June 13, 2023, 5:04pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538 "2023-06-13T17:04:33Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 13, 2023, 5:04pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/1 "2023-06-13T17:04:33Z")
- What Grafana version and what operating system are you using? Grafana 9.3.2 - **What** are you trying to achieve? Visualize in a bar graph the weekly consumption based on daily values for one month - **How** are you trying to achieve it? import “timezone” option location = timezone.location(name: “Europe/Berlin”) from(bucket: “iobroker”) |\> range(start: -1mo, stop: now()) |\> filter(fn: (r) =\> r[“\_measurement”] == “HausverbrauchKwh” and r[“\_field”] == “value”) |\> aggregateWindow(every: 24h, fn: last, createEmpty: false) |\> truncateTimeColumn(unit: 1m) |\> aggregateWindow(every: 1w, fn: sum, createEmpty: false) |\> timeShift(duration: -1d) - What happened? - What did you **expect** to happen? Every week bar should last from Monday (including) until Sunday (including). So this week starting with 12.6. (last bar → OK). Penultimate bar starting with 5.6. (not OK, as currently 7.6.). In general the values per day are accumulated per day in the source system. So the highest value is the last for the day (therefore I used last). This week (12.6. - 13.6.): 29,49 kWh + 43,58 kWh = 73,07 kWh (not 172) Last week (5.6. - 11.6.): 24,19+38,98+26,29+42,49+29,25+40,77+3,26 = 205,23 kWh - Did you follow any online instructions? If so, what is the URL? tried with: [https://community.grafana.com/t/influxdb2-power-consumption-group-by-month/77822/15](https://community.grafana.com/t/influxdb2-power-consumption-group-by-month/77822/15) ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [June 13, 2023, 5:47pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/2 "2023-06-13T17:47:37Z")
what do you see when you run this query in influx itself? please post screen shot if possible? and how are you writing to influxdb and are you sure the dates are utc? ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 13, 2023, 6:17pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/3 "2023-06-13T18:17:28Z")
you mean that: ![image](https://us1.discourse-cdn.com/grafana/original/3X/9/0/905ecf169f442d7ae7d738b72ad55ddeab385081.png) ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [June 13, 2023, 6:46pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/4 "2023-06-13T18:46:39Z")
yep. and does it show accurately in influxdb explorer? ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [June 13, 2023, 6:46pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/5 "2023-06-13T18:46:54Z")
> [@yosiasz](#): > > and how are you writing to influxdb and are you sure the dates are utc? you have not answered this critical question ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 13, 2023, 7:23pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/6 "2023-06-13T19:23:05Z")
I write to influxdb via iobroker-adapter Not quiet sure if the dates are really utc. How can I find out? And no, in influxdb explorer it is also not correct ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [June 13, 2023, 7:47pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/7 "2023-06-13T19:47:59Z")
Check iobroker adapter config maybe for time settings or server is is being run on etc Run it in debug mode or see what it is dumping test mode to test bucket and compare to real utc ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [June 13, 2023, 8:02pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/8 "2023-06-13T20:02:23Z")
> [@dieterlind](#): > > import “timezone” > option location = timezone.location(name: “Europe/Berlin”) Could also be ☝🏿☝🏿 ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 14, 2023, 6:56am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/9 "2023-06-14T06:56:35Z")
just checked the settings in iobroker-adapter: nothing to set there regarding time server itself shows the right time timedatectl Local time: Wed 2023-06-14 08:50:08 CEST Universal time: Wed 2023-06-14 06:50:08 UTC when I run it without timezone-settings I receive ![image](https://us1.discourse-cdn.com/grafana/original/3X/d/2/d261217ccb6d104b000bd9a783260ec3f753a70e.png) with timezone-setting ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/9/69a3a0efb1a06eeafe9845f0490d62d19aebcac4.png) so obviously both not correct … Debug mode: In grafana or indlux?? Anyway: How can I enable it? ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 14, 2023, 7:08am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/10 "2023-06-14T07:08:11Z")
as additional information: When I just evaluate the last value per day everything is OK: ![image](https://us1.discourse-cdn.com/grafana/original/3X/7/1/71e11007e107ba227271b7d1b990d6828421d603.png) so it has something to do with the sum over one week ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [June 14, 2023, 9:42am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/11 "2023-06-14T09:42:10Z")
> [@dieterlind](#): > > |\> aggregateWindow(every: 24h, fn: last, createEmpty: false) Is there a reason you chose in the above line 24h instead of 1d? ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 14, 2023, 9:58am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/12 "2023-06-14T09:58:37Z")
actually not … but I just tried and it didn’t make a difference ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [June 15, 2023, 9:23am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/13 "2023-06-15T09:23:38Z")
Hi @dieterlind You wrote that when you evaluate the last value per day, everything is OK: ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/9/695811333639d1762f5e2e0e8900b511d801527d.png) Can you show your Flux query for the above graph? Also, please remove the Relative Time = 1d in the query options just to get the “plain” results. ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 16, 2023, 5:02am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/14 "2023-06-16T05:02:41Z")
The Flux query for the above graph is: import “timezone” option location = timezone.location(name: “Europe/Berlin”) from(bucket: “iobroker”) |\> range(start: -8d) |\> filter(fn: (r) =\> r[“\_measurement”] == “HausverbrauchKwh” and r[“\_field”] == “value”) |\> aggregateWindow(every: 24h, fn: last, timeSrc: “\_start”) current graph without relative time = 1d: ![image](https://us1.discourse-cdn.com/grafana/original/3X/e/0/e0a4ca98dd11752dbedffd0e6baa0b5dc48d5868.png) ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [June 16, 2023, 11:17am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/15 "2023-06-16T11:17:34Z")
Just kicking around ideas here… Since you want to aggregate the data by week starting on a Monday, and knowing the last value in a given week (the highest value) is the actual usage for the week, how about this: ```auto import "timezone" option location = timezone.location(name: "Europe/Berlin") from(bucket: "iobroker") |> range(start: -8d) |> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value") |> aggregateWindow(every: 1w, offset: -3d, fn: last) // see comment below ``` _Flux increments weeks from the Unix epoch, which was a Thursday. Because of this, by default, all `1w` windows begin on Thursday. Use the `offset` parameter of -3d to shift the start of weekly windows to Monday._ Just thought of a question…Does the “counter” which reaches the highest value at 23:59 every day reset to 0 at 00:00? ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 16, 2023, 11:40am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/16 "2023-06-16T11:40:35Z")
when doing your flux I get: ![grafik](https://us1.discourse-cdn.com/grafana/original/3X/0/0/00aaf26a73997c961f85cc103580c70db3c327e5.png) and yes: the counter starts every day with 0 ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [June 16, 2023, 11:59am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/17 "2023-06-16T11:59:58Z")
How about this? ```auto import "timezone" option location = timezone.location(name: "Europe/Berlin") from(bucket: "iobroker") |> range(start: -8d) |> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value") |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread |> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week ``` ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 16, 2023, 5:15pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/18 "2023-06-16T17:15:48Z")
result: ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/1/6183e019575bbecb06d1b1340cba1fae967a4038.png) somehow not what I expect I would expect the last 4 weeks, last week starting with 12.6. summing up to approx. 165 kWh ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [June 16, 2023, 5:55pm UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/19 "2023-06-16T17:55:34Z")
> [@grant2](#): > > ` |> range(start: -8d)` Change the above to -28d or something more and see how each weekly brick looks. ---
**Author:** ![dieterlind](https://avatars.discourse-cdn.com/v4/letter/d/a88e57/32.png) [@dieterlind](https://community.grafana.com/u/dieterlind)\ **Post date:** [June 17, 2023, 9:32am UTC](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538/20 "2023-06-17T09:32:43Z")
OK … getting closer. With this flux: ```auto import "timezone" option location = timezone.location(name: "Europe/Berlin") from(bucket: "iobroker") |> range(start: -1mo) |> filter(fn: (r) => r["_measurement"] == "HausverbrauchKwh" and r["_field"] == "value") |> aggregateWindow(every: 1d, fn: last, createEmpty: false) // calculates the amount per day using the spread |> aggregateWindow(every: 1w, offset: -3d, fn: sum) // sums the 7 days of each week to get the total used per week ``` I get this result ![image](https://us1.discourse-cdn.com/grafana/original/3X/0/e/0e9196fe5c44e0d7554a2beba77b3cda9f870364.png) Which is not completly wrong but also not really right 😉 Why: What I would like to have that e.g. the last bar hast the label 12.6. with all the consumption since including 12.6. including today. With my figures this would mean 181 kWh. What it does is to sum up from 11.6. until 16.6 (today missing). The penultimate bar should have the label 5.6. summing up the values from 5.6. until 11.6. which would be 205 kWh. What at the moment happens is the sum from 4.6. until 10.6. Maybe to make it easier to understand here the values form the last month: | Date | kWh | expected values | current value | | --- | --- | --- | --- | | 18.05.2023 | 34,7 | 177,9 | 120,3 | | 19.05.2023 | 37,3 | | | | 20.05.2023 | 48,3 | | | | 21.05.2023 | 57,6 | | 322,2 | | 22.05.2023 | 45,2 | 300,2 | | | 23.05.2023 | 44,8 | | | | 24.05.2023 | 46 | | | | 25.05.2023 | 45,9 | | | | 26.05.2023 | 39,6 | | | | 27.05.2023 | 43,1 | | | | 28.05.2023 | 35,6 | | 270,6 | | 29.05.2023 | 35,1 | 274 | | | 30.05.2023 | 40,2 | | | | 31.05.2023 | 26,7 | | | | 01.06.2023 | 47,1 | | | | 02.06.2023 | 42,6 | | | | 03.06.2023 | 43,3 | | | | 04.06.2023 | 39 | | 241,1 | | 05.06.2023 | 24,2 | 205,36 | | | 06.06.2023 | 39 | | | | 07.06.2023 | 26,3 | | | | 08.06.2023 | 42,5 | | | | 09.06.2023 | 29,3 | | | | 10.06.2023 | 40,8 | | | | 11.06.2023 | 3,26 | | 183,96 | | 12.06.2023 | 29,5 | 180,7 | | | 13.06.2023 | 47,1 | | | | 14.06.2023 | 26,1 | | | | 15.06.2023 | 40,6 | | | | 16.06.2023 | 26,1 | | | | 17.06.2023 | 11,3 | | | [Next page](https://community.grafana.com/t/influxdb2-combining-last-sum-to-get-weekly-values/89538.md?page=2)