# Flux max() - min() for two different tags aggregated per day **URL:** \ **Category:** Fluxlang\ **Tags:** flux\ **Created:** [September 6, 2021, 10:48pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585 "2021-09-06T22:48:36Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![trunet](https://avatars.discourse-cdn.com/v4/letter/t/3da27b/32.png) [@trunet](https://community.influxdata.com/u/trunet)\ **Post date:** [September 6, 2021, 10:48pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/1 "2021-09-06T22:48:36Z")
Hello, I’m an old school relational SQL guy. I’m trying to build an energy dashboard on grafana using flux. Home assistant is feeding influxdb 2.0. I have a kWh measurement and entity\_id tag (among other tags irrelevant to this). I have two measurements on my smart meter and that’s why there’re 2 entity\_id tags. This is my data, for simplicity, let’s assume \_time is hours on same day, but I have this every 30 seconds or so… \_time | entity\_id | \_measurement 1h | power\_consumption\_1 | 1000 1h | power\_consumption\_1 | 50 2h | power\_consumption\_1 | 1010 2h | power\_consumption\_1 | 50 3h | power\_consumption\_1 | 1020 3h | power\_consumption\_1 | 50 4h | power\_consumption\_1 | 1020 4h | power\_consumption\_1 | 60 5h | power\_consumption\_1 | 1020 5h | power\_consumption\_1 | 70 I came to this working monstrous query: ```auto data = from(bucket: "homeassistant") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "kWh") |> filter(fn: (r) => r["_field"] == "value") |> drop(columns: ["source", "friendly_name"]) min1 = data |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_1") |> drop(columns: ["entity_id"]) |> aggregateWindow(every: 1d, fn: min) max1 = data |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_1") |> drop(columns: ["entity_id"]) |> aggregateWindow(every: 1d, fn: max) min2 = data |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_2") |> drop(columns: ["entity_id"]) |> aggregateWindow(every: 1d, fn: min) max2 = data |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_2") |> drop(columns: ["entity_id"]) |> aggregateWindow(every: 1d, fn: max) join1 = join(tables: {min1: min1, max1: max1}, on: ["_start", "_stop", "_time", "_field", "_measurement", "domain"], method: "inner") |> map(fn: (r) => ({ r with _value: r._value_max1 - r._value_min1 })) join2 = join(tables: {min2: min2, max2: max2}, on: ["_start", "_stop", "_time", "_field", "_measurement", "domain"], method: "inner") |> map(fn: (r) => ({ r with _value: r._value_max2 - r._value_min2 })) join(tables: {join1: join1, join2: join2}, on: ["_start", "_stop", "_time", "_field", "_measurement", "domain"], method: "inner") |> map(fn: (r) => ({ r with _value: r._value_join1 + r._value_join2 })) |> drop(columns: ["_value_join1", "_value_join2", "_value_max1", "_value_max2", "_value_min1", "_value_min2"]) ``` Now, this is working, it’s building the table I want. But, there should be a simpler/faster/optimized way, can somebody help me here? Best regards, Trunet ---
**Author:** ![trunet](https://avatars.discourse-cdn.com/v4/letter/t/3da27b/32.png) [@trunet](https://community.influxdata.com/u/trunet)\ **Post date:** [September 7, 2021, 12:43am UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/2 "2021-09-07T00:43:48Z")
I managed to optimize a little bit. Are there better optimization here? ```auto data1 = from(bucket: "homeassistant") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "kWh") |> filter(fn: (r) => r["_field"] == "value") |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_1") |> drop(columns: ["source", "friendly_name"]) |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) data2 = from(bucket: "homeassistant") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "kWh") |> filter(fn: (r) => r["_field"] == "value") |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_2") |> drop(columns: ["source", "friendly_name"]) |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) join(tables: {data1: data1, data2: data2}, on: ["_start", "_stop", "_field", "_measurement", "domain", "_time"]) |> map(fn: (r) => ({ r with _value : r._value_data1 + r._value_data2 })) ``` ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [September 9, 2021, 7:46pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/3 "2021-09-09T19:46:21Z")
Hello @trunet, First of all thanks for giving Flux a try. It definitely takes a second to transition your thinking. Can you try the following? ```auto from(bucket: "homeassistant") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "kWh") |> filter(fn: (r) => r["_field"] == "value") |> filter(fn: (r) => r["entity_id"] == "energy_consumption_tarif_1" or r["entity_id"] == "energy_consumption_tarif_2") |> drop(columns: ["source", "friendly_name"]) |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) |> group() |> pivot(rowKey:["_time"], columnKey: ["entity_id"], valueColumn: "_value") |> map(fn: (r) => ({ r with _value : r.energy_consumption_tarif_1 + r.energy_consumption_tarif_2 })) ``` Please let me know if it doesn’t work or explain further. ---
**Author:** ![trunet](https://avatars.discourse-cdn.com/v4/letter/t/3da27b/32.png) [@trunet](https://community.influxdata.com/u/trunet)\ **Post date:** [September 13, 2021, 11:29am UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/4 "2021-09-13T11:29:15Z")
This worked perfectly, thank you. Didn’t know about the pivot() function. ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 14, 2022, 1:11am UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/5 "2022-06-14T01:11:49Z")
Hi, I have grouped together several measurements into one table using ‘pivot’, but is there a way to select the maximum of each row using ‘map’? I have 8 measurements now per time sample, and would like to only select the maximum measurement per sample. The ‘max’ function looks to be only used to calculate the maximum value in a column and not a row-wise operation. ---
**Author:** ![Patrick808](https://avatars.discourse-cdn.com/v4/letter/p/b487fb/32.png) [@Patrick808](https://community.influxdata.com/u/Patrick808)\ **Post date:** [June 14, 2022, 4:08pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/6 "2022-06-14T16:08:50Z")
What are you looking for as your output? Do you just want the value or do you need the value and column heading? ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 14, 2022, 4:21pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/7 "2022-06-14T16:21:20Z")
Really just looking for the value. Planning on calculating the max/min values and then doing a join of those two queries and a map to calculate the difference between them. If there’s a better way to do this in Flux, I’m all ears… Thanks ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 20, 2022, 9:23pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/8 "2022-06-20T21:23:20Z")
@Anaisdg - could you please assist? Looks like you were able to resolve the previous issue easily. I am thinking this should be a basic functionality and I’m wondering if I’m missing something that’s available in Flux. Thanks ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [June 21, 2022, 3:49pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/9 "2022-06-21T15:49:36Z")
> [@epraetor\_flux](#): > > 8 measurements now per time sample If you want the max of these 8 measurements per time sample, you would use the aggregateWindow() function without grouping or pivoting. > **[aggregateWindow() function | Flux Documentation](https://docs.influxdata.com/flux/v0/stdlib/universe/aggregatewindow/)** > > aggregateWindow() downsamples data by grouping data into fixed windows of time and applying an aggregate or selector function to each window. ```auto from(bucket: "mybucket") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "1" or r["_measurement"] == "2"... or r["_measurement"] == "8") |> aggregateWindow( every: 1m, fn: mean) ``` ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 21, 2022, 4:26pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/10 "2022-06-21T16:26:21Z")
I am already doing an aggregate window, but this provides the max for each separate measurement per time window. What I’m hoping to accomplish is to only return the maximum of all eight measurements. ```auto from(bucket: "bucketName") |> range(start: v.timeRangeStart, stop:v.timeRangeStop) |> filter(fn: (r) => r._measurement == "Battery Container" and (r._field == "Temperature3" or r._field == "Temperature4" or r._field == "Temperature5" or r._field == "Temperature6" or r._field == "Temperature11" or r._field == "Temperature12" or r._field == "Temperature13" or r._field == "Temperature14") and r.instance == "${container}") |> aggregateWindow(every: 60s, fn: mean, createEmpty: false) |> drop(columns: ["source"]) ``` ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 21, 2022, 4:28pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/11 "2022-06-21T16:28:00Z")
Ah I added group() command and this seems to do what I was looking for _facepalm_ Thanks for responding ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [June 22, 2022, 5:45pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/12 "2022-06-22T17:45:19Z")
@epraetor_flux face palm is my mo, that’s what we’re here for. Out of curiosity what are you using Influx for? Thanks for sharing your solution. ---
**Author:** ![epraetor\_flux](https://avatars.discourse-cdn.com/v4/letter/e/bb73d2/32.png) [@epraetor\_flux](https://community.influxdata.com/u/epraetor_flux)\ **Post date:** [June 22, 2022, 5:57pm UTC](https://community.influxdata.com/t/flux-max-min-for-two-different-tags-aggregated-per-day/21585/13 "2022-06-22T17:57:19Z")
Visualizing battery energy storage data with Grafana