Summary and representation of daily to calendar monthly values

[say1897]

Hi everyone,

I’m completely new to Grafana, but I’m already feeling a bit stuck. Maybe you can help me get started so that I can dive into the topic better with some hands-on experience and assistance.

Ich hoffe ich bin hier richtig.

• What Grafana version and what operating system are you using?
  Grafana 9.2.2 on Homeassistant

• What are you trying to achieve?
  I have a small PV system and I want to display the generated electricity on a monthly basis in kWh.

My Database in Influx DB.

Here are the specific requirements:

• The calendar months should be summed up (not just 30 days).
• The data for the generated electricity is available on a daily basis.
• It should be represented as a bar chart.
• The Y-axis should be labeled in kWh.

The following is my current status.

SELECT mean("value") FROM "Wh" WHERE ("friendly_name"::tag = 'BKW YieldDay') AND $timeFilter GROUP BY time($__interval) fill(null)

Can you give me some tips and nudge me in the right direction?

Thank you in advance!

[grant2]

Welcome @say1897 to the Grafana forum.

Unfortunately, you have set up Grafana & Influx to use InfluxQL. If you can switch to Flux or SQL, then you can aggregate by calendar month as you desire.

See the last table in this blog post:

InfluxQL
Only basic aggregations (day, week) are possible at this time

Here is an example of Flux query aggregating by calendar month.

from(bucket: "EIAtest7")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ElectricPowerOperations")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["type"] =~ /^${type:regex}$/ )
  |> filter(fn: (r) => r["region"] =~ /^${region:regex}$/ )
  |> group(columns: ["region", "_measurement"], mode:"by") 
  |> aggregateWindow(every: 1mo, offset: 1d, fn: sum, timeSrc:"_start")  // sums the total used per month
  |> yield(name: "monthly_data")

[say1897]

Alright! Now I’m thoroughly confused! At least with the help of InfluxDB, I managed to output something

But I have no idea how to proceed even remotely from here.

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")

[grant2]

You may find it easier to work in Influx Data Explorer before pasting your query into Grafana. The URL is usually http://your-ip-address:8086

This is just a wild guess…

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> filter(fn: (r) => r.domain == "sensor")
  |> filter(fn: (r) => r.friendly_name == "BKW YieldDay")
  |> filter(fn: (r) => r.name == "Wh")
  |> filter(fn: (r) => r.source == "HA")
  |> aggregateWindow(every: 1mo, offset: 1d, fn: sum, timeSrc:"_start")  // displays the total used per month
  |> yield(name: "monthly_data")

The above will likely not yield the correct result, because from the graph you shared, it appears your kW value increases every 24h, then goes back to 0? There is a way using Flux to correctly use that info to calculate your monthly usage. For now, does the above query give you back a monthly graph?

[say1897]

In BKW Yieldday, the total yield is monitored each day. Since the value naturally increases throughout the day, the curve always trends upwards. At the end of the day, the counter is reset to 0.

When I insert the code into InfluxDB or Grafana, I receive an error message.

[grant2]

Please replace the aggregateWindow function like this & see if the error goes away.

  |> aggregateWindow(every: 1mo, fn: sum, timeSrc:"_start")  // displays the total used per month

EDIT: Better yet, try this:

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> filter(fn: (r) => r.domain == "sensor")
  |> filter(fn: (r) => r.friendly_name == "BKW YieldDay")
  |> filter(fn: (r) => r.name == "Wh")
  |> filter(fn: (r) => r.source == "HA")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
  |> sum() // sums the above 24-hour periods
  |> yield(name: "monthly_data")

[say1897]

I don’t get any error message anymore! However, I also don’t get any values.

[grant2]

What is the time window selected? Can you expand it to last 7 days or something like that?

[say1897]

I’ve already tried that too. It doesn’t matter what I set. No values are coming through like this.

If I only store the entity, I can display the seven days. I get an error message for 30 days.

If I switch to InfluxQL, I can easily display the 30 days without any problems.

[grant2]

Looks like the stop time was not specified in the range() function.

Can you try this?

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
  |> sum() // sums the above 24-hour periods
  |> yield(name: "monthly_data")

[say1897]

First of all…thank you for your support. That’s amazing.

This code unfortunately didn’t work either.

[grant2]

You are getting close…

Please try this…

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop) 
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> filter(fn: (r) => r.domain == "sensor")
  |> filter(fn: (r) => r.friendly_name == "BKW YieldDay")
  |> filter(fn: (r) => r.name == "Wh")
  |> filter(fn: (r) => r.source == "HA")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
  |> sum() // sums the above 24-hour periods
  |> yield(name: "monthly_data")

If the above still yields the same error, then I have an idea, but I would need to see your data in Influx Data Explorer with the Raw Data value toggled on (so I can see what data types you have for domain, friendly_name, name, source, etc.).

[say1897]

You mean this?

I can provide you with the data from the last 24 hours as a CSV file. Just tell me how…

[grant2]

OK, so we got rid of the aggregateWindow error message, but still no data. From the Raw Data you shared, I can see the _value field seems to contain a string field called “energy”. You can try this, but I think it still will not work…

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop) 
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> filter(fn: (r) => r.domain == "sensor")
  |> filter(fn: (r) => r.friendly_name == "BKW YieldDay")
  |> filter(fn: (r) => r.name == "Wh")
  |> filter(fn: (r) => r.source == "HA")
  |> filter(fn: (r) => r._value == "energy")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
  |> sum() // sums the above 24-hour periods
  |> yield(name: "monthly_data")

Assuming the above does not work, can you toggle Raw Data in Influx Data Explorer and show what you see when you run this query (the one you shared in the beginning of the thread that showed some data/graphs).

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> yield(name: "test")

[say1897]

The code doesn’t work as expected. No data.
The _value field has multiple values.
One time it is “energy”. Then there is “total_increasing” and also the correct values as a number.

[grant2]

How about this?

  from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop) 
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")
  |> filter(fn: (r) => r.domain == "sensor")
  |> filter(fn: (r) => r.friendly_name == "BKW YieldDay")
  |> filter(fn: (r) => r.name == "Wh")
  |> filter(fn: (r) => r.source == "HA")
  |> filter(fn: (r) => r._value >= 0)
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false) // calculates the amount per day using the spread
  |> sum() // sums the above 24-hour periods
  |> yield(name: "monthly_data")

[say1897]

No Results

[grant2]

@say1897

I can provide you with the data from the last 24 hours as a CSV file. Just tell me how…

Let’s try what you suggested above, but let’s do 7 days. In Influx Data Explorer, change the time selector to Last 7 days and in the query builder put this:

from(bucket: "home_assistant/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r.entity_id == "bkw_yieldday")

then (assuming it generated some data) download the CSV file using the button. You can post here or send to me a private message if you prefer.

[say1897]

7 days are no problem either. I created the file, but I can’t upload it here. CSV files don’t seem to be allowed.

Where can I send you a PM? I can’t find a button for it,

[yosiasz]

copy paste the content of csv here

energy,day
33.5,2024-04-29