Get the highest 15 minute average per month of a power graph

homemonitor · March 6, 2024, 3:44pm

Grafana v10.3.3 (252761264e)
Influx 2.x.y

In Influx I have the power consumption and power generation (+ for using from grid / - for PV generation) for my home.
I want to generate a bar graph for a given year which

Gets all power data and averages this per 15m (0-15-30-45-0)
Out of all these averages in a month, I want the bar to show the highest average of that month.

I keep having problems with the syntax or getting to know the Flux language.

In pseudo code this would be something like

Timeframe == current year
Get all power
average per 15 min
per month, select maximum of that month

The challenge i have is in the last part/item of the pseudo code, in the old days that would be with “group by” I think. But also when I try tinker in Influx Studio the result right now looks like it is counting the instances rather than averaging them out.

from(bucket: "EnergyFlow")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy")
  |> filter(fn: (r) => r["_field"] == "Total_W")
  |> filter(fn: (r) => r["meter"] == "main")
  |> filter(fn: (r) => r._value >= 0)
  |> aggregateWindow(every: 15m, fn: mean, createEmpty: false)
  |> yield(name: "mean")

→

yosiasz · March 7, 2024, 2:06am

What are the results you see when running this within influxdb itself then maybe walk up to monthly then to max 15 of month

Also wonder if the window() function would be better suited for this

dataSet
    |> window(every: 1m)
    |> mean()
    |> duplicate(column: "_stop", as: "_time")
    |> window(every: 1month)

Kind of thing

grant2 · March 7, 2024, 3:59am

Couldn’t one just use two consecutive aggregateWindow statements (first to get the 15-min averages of ALL the data, then a second one to aggregate by month to the get the max 15-minute average for each month)?

  |> aggregateWindow(every: 15m, fn: mean, createEmpty: false)
  |> aggregateWindow(every: 1mo, fn: max)

homemonitor · March 7, 2024, 8:03am

When I executed my initial query it shows in influx as the blue bars which i screenshotted above.
The raw data looked like this:

homemonitor · March 7, 2024, 8:06am

This was the solution, thank you @grant2.
It was as simple as you showed. I am not yet that familiar with the Flux, I find it very hard to be able to convert my thinking from “Oracle/MSSQL alike queries” towards the Flux-flow.

Thank you @yosiasz for your insights on the problem.

This is how it looks now in Grafana (which is already very nice):

I have now used a bar graph instead of a time series. How can I change the time 02:00 or 01:00 to show month values?

@grant2 i saw an answer to a thread asking the same thing (How can I change graph X-axis to show days of the week(Monday, Tuesday, Wednesday etc)) but setting it to time:1mo doesn’t change anything.

@yosiasz I saw your input here too: Struggeling with bar chart for monthly consumption

Suppose I would like to create a “drilldown” clickthrough panel, I see if you hover over the bars it “color selects them visually” which triggers you to click through to that relevant month and show all peaks per day and then again clicking through so you can see the exact moment. How would one do that?

grant2 · March 7, 2024, 12:28pm

Hi @homemonitor

Glad you are making progress.

To get the x-axis labels to display the months on the Bar chart, have you tried this?

override:

result:

To get the exact 15-minute window of the day of each month where the peak occurred, you would modify your Flux query to something like that shown below (warning this may be wrong as I cannot test it without your data). I’d recommend you do it in Influx Data Explorer as it will be easier to debug there vs Grafana. Once it’s good, copy/paste into Grafana and maybe use a Table visualization showing the 12 months of the current year with the peak day/time for each month.

from(bucket: "EnergyFlow")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy")
  |> filter(fn: (r) => r["_field"] == "Total_W")
  |> filter(fn: (r) => r["meter"] == "main")
  |> filter(fn: (r) => r._value >= 0)
  |> aggregateWindow(every: 15m, fn: mean, createEmpty: false)
  |> aggregateWindow(every: 1mo, fn: max)
  |> keep(columns: ["_time", "_value"])  
  |> group()
  |> yield(name: "peak_each_month")

grant2 · March 7, 2024, 12:30pm

You are not alone. InfluxData came to the same conclusion about a year ago and (after promoting Flux for years) made a sudden 180 to move everything to SQL in Influx v3, and put Flux into “Maintenance Mode”.

homemonitor · March 7, 2024, 12:48pm

Jup, the time:MM/YYYY works beter. I was using the time:mo parameter but that shows a bit different than yours. Yours is what I wanted to get. Can you also request Grafana to format the months as the names instead of month/day or remove the day? Currently shows as follows but I am European/Belgian so our notation is DD/MM/YYY, now I am triggered by the first 01 after every month

I haven’t been working with the “drill through” aspect of Grafana but I saw somewhere you can use dynamic parameters when you click on a visualisation to pass them to a secondary graph which shows a more detailed view on that data. That’s what I want to achieve.

The query is completely valid but not yet showing the moment the peak occurred but also that’s not really the goal. I would like to click and be able to show that peak in a more detailed overview so I can flip through all the days and see “ah yes then it ocurred too”. So a direct GO TO PEAK MOMENT is nice and would be a good excercise to work with the dynamic parameters but I am already happy to be able to click through to a month graph showing all the peaks and being able to sift through them there.

By the way, this is the total overview, love it.

grant2 · March 8, 2024, 2:11am

Hi @homemonitor

This is a query showing the day & hour (for the selected time range, which could be 5 days or 5 years) of the peak value, separated by region.