Moving Average function combined with difference does not give wanted results

Grafana Time Series Panel
1

Hi,

I just started with Grafana in Home Assistant with influxDB.

I wanted to make a moving average of the last 24 hours or the last 365 days of my energy usage. I have a Home Wizard P1 that reports the total consumption. I use the time series panel for this in Grafana.

I have got it almost working but i notice some strange behaviour. Because the P1 delivers total consumption i use the function “Difference” in grafana. This works great. Then i use the function “moving average(24)” which also seems to work great. I also use group by so i get consumption per hour.

However, the moving average seems to not include data points that have value 0. Only data points that have values lager than 0. This is a problem because i have solar panels so when the sun shines i have no consumption. And the moving average number of 24 (hours) or 365 (days) is critical because it can smooth out effect over the day or over the year (seasonal effects).

Is the difference function not working correctly or can i do something to include data points that have value 0? Or does this have something to do with the fact that is use total energy consumption and do i have to create a variabele in home assistant that measures hourly and daily usage?

Edit: when i view the data in the table view i see there are no data points when there has been no change. So moving average will not work correctly with difference. Maybe there is a way to use the difference function so that it writes 0 if there is no change?

Best regards,
Martijn

2

In your influxdb are there records or rows with consumption of 0?
What is the time interval of data recorded

3

No.

In my influxdb is only the total consumption of the electricity meter from the moment the meter was installed (10 years ago). A so called meter reading. So i have a consumption of 4000 kwh per year so the meter reading is about 40000. The next moment it is 40001, etc.

When i visualise the data in influxdb is see that when the meter reading does not change for one hour the mouse snaps only on the data point at the beginning and end. When i export to csv i see values every minute. When there is no change there is no value only an empty cell.

4

Please post image what this empty cell looks like

Maybe you can convert empty cells to 0