Energy dashboard screwed due to decreasing input sensor - how to a) fix and b) prevent in future?

[e-raser]

Today it happened to me too… energy dashboard screwed due to sensor fault in “grid usage” input sensor.

I accidentally noticed this:

I can’t have negative values. Only taking power from the grid currently.

This input senor is generated using this yaml:

      energy_total_total:
        friendly_name: Stromverbrauch Alle Gesamt
        unit_of_measurement: "kWh"
        value_template: >-
          {{
            (
              states('sensor.energy_1_total') | float(0) +
              states('sensor.energy_2_total') | float(0) +
              states('sensor.energy_3_total') | float(0) +
              ...
            ) | round(2)
          }}

where the sensor.energy_X_total are utility meters, each with simple (no reset etc.)

  energy_X_total:
    source: sensor.power_plug_X_total_consumption

In customize.yaml I use

sensor.energy_total_total:
  friendly_name: Stromverbrauch Alle gesamt
  icon: mdi:counter
  device_class: energy
  state_class: total
  last_reset: "2021-06-04T00:00:00+00:00"

Reason is, that a part of all input sensors was not available. Due to my calculation template above, the sum is calculated without those. Unfortunately that happened quite often as history shows…

…but luckily it never happened right at the snapshot time when LTS is being generated. This was the case today for the 1st time (part of the input sensors like sensor.energy_2_total were unavailable) right from 15:59:14 until 16:00:07, so that the LTS generation at 16:00:00 catched the dropped value and… et voila, the energy dashboard is screwed.

Todays’ drop/incident is exactly 616,13 kWh.

Now I see two (urgent) actions:

1. Fix the current states + statistics + statistics_short_term tables data to fix the energy dashboard. I did something similar in the past already and am quite confident being able to fix this in the backend with some SQL magic.
   OR (likely saving me 2 hours of time, at least - compared to the SQL fixing):
   how would I fix this in the /developer-tools/statistics section properly?
   

   

2. Prevent this to happen again. The entities value NEVER can decrease, only an increase is possible. Ideas:

• 2.a) Would using state_class: total_increasing instead of state_class: total achieve this? TBH I read Sensor Entity | Home Assistant Developer Docs twice and still don’t know if this is the right setting to achieve what I’m looking for (sensor value can only stay or increase, but never decrease).
• 2.b) Adjust the energy_total_total sensor definition: check if all sensors are not in ['0', 'NULL', 'unavailable', 'unknown'] and only calculate the sensor value then. Like:

{% if sensor_1 not in ['0', 'NULL', 'unavailable', 'unknown'] and
      sensor_2 not in ['0', 'NULL', 'unavailable', 'unknown'] and
      sensor_3 not in ['0', 'NULL', 'unavailable', 'unknown'] and
      ...
      ... %}
{% set total_energy = (sensor_1 | float(0) + sensor_2 | float(0) + sensor_3 | float(0) + ...) | round(2) %}
{{ total_energy }}
{% endif %}

Can you please advise on

• task 1 and
• what’s the best way for ticking task 2

Thank you in advance

[Edwin_D]

As to fixing the negative value:You found the screen to fix it, just click on ithe -616.13 value and set the value to 0. But you should then also do it to the corresponding +616.15 value (which you should set to 0.02 to be exact).

But you don’t have to, as they cancel each other out. value. The statistics saw it disappear and reappear. So you might also decide to leave it as is, as netto there isn’t a problem, only if you look closely in time.

As for preventing it: add an availability template that sets the sensor unavailable if either component is unavailable.

see this topic for an example on how to set an availability template. Although I would use is_number() to test if the state is something you can add:

Availability template

Can someone tell please me how to apply the availability template to my situation? Even with the examples I’ve seen, I keep getting errors. I want to prevent my utility meters from showing wrong values ​​if the sensors are not available. Like in this example. sensor: - platform: rest name: meter_a json_attributes: - waterflow - waterquantity resource: http://192.168.0.101/water.html - platform: rest name: meter_b json_attributes: - waterflow - w…

[e-raser]

@Edwin_D

As to fixing the negative value:You found the screento fix it, just click on ithe -616.13 value and set the value to 0. But you should then also do it to the corresponding +616.15 value (which you should set to 0.02 to be exact).

1. This will fix tables statistics and statistics_short_term, but not states, right? Latter one might be interesting for graphs, at least until the recorder retention time is reached and they are removed from the database anyway. So: using the dev-tools statistics section is sufficient for fixing the energy dashboard?

@Edwin_D

But you don’t have to, as they cancel each other out. value. The statistics saw it disappear and reappear. So you might also decide to leave it as is, as netto there isn’t a problem, only if you look closely in time.

2. Are you sure about this? I expect the energy dashboard to continue the negative value for week, month and year. For week once today has passed (and so on). Won’t it?

@Edwin_D

As for preventing it: add an availability template that sets the sensor unavailable if either component is unavailable.

3. I came across this idea too already. Would be quite easy to add, as I could leave my template sensor definition mostly as it is and just add the availability_template. But then I came across Wofür verwendet man die Utility Meter Integration? - #17 von Soul - Integrationen - simon42 Community where one users states (in German):

With the “availability_template” you should probably be able to query whether the plug outputs a value or number at all at that moment, e.g. if NULL or unavailable, the values should remain unchanged or not be falsified. But it didn’t work for me anyway

The “But it didn’t work for me anyway” made me think of “ahm, probably not the right solution then?!”

[Edwin_D]

@e-raser

but not states, right?

No, but that problem is indeed gone once the retention period (default 10 days) is over.

@e-raser

continue the negative value for week

You will see it within day level, but on daytotal and larger periods, it cancels out. But edit them both if you want it clean. Just realise the plus version is also wrong. You noticed the same behaviour when it happens within the hour: if is canceled (but it was registered in statistics). It is only because it happened around the hour that you can see it.

@e-raser

But it didn’t work for me anyway

It depends on what happens to your three sensors when they are unavailable. If you miss availability templates there too and one of the sensors goes to 0 instead of unavailable, then another test might be of more use in the availability template. But the availability template is the way to go. If it does not work, you didn’t have the right template. If only one component is unavailable, the sum should be unavailable.

[e-raser]

I took a look in the history, using statistics card set to 365 days with hour data. And there are more incidents of that type, as already shown with:

But today is not the first incident/time, it had an actual impact on the LTS and energy dashboard data. Here filtered for 180 days to have a better scale:

The first incident was on 2023-11-13. There were much more since then, checking every single day in the energy dashboard:

List:

• 2023-11-13
• 2023-11-16
• 2024-02-10

On 2023-11-13 I replaced an UPS and did some tests with one Shelly Plug the UPS is attached to see/test if the UPS works (and how long). On 2023-11-15 there was nothing notable. I think that all doesn’t matter as it’s a combination of

• template sensor calculating wrong and
• doing this exactly when LTS is generated (every exactly 5 minutes)

Interestingly I updated to HA Core 2023.4.6 on 2023-11-04.
That seems to be no coincidence, looking at

Negative Values in Energy Dashboard since last update

To summarize two questions: This was introduced with HA Core 2023.5 and only affected Riemann Sum integration. Right? Which HA Core release fixed this issue? I went through all posts but could not find a PR for the fix. I think I might have run into the same issue once updating to HA Core 2023.4.6 back in November. Since then I see this regularly and only discovered today… Edit: I made some more research in my InfluxDB and I can confirm updating HA Core to 2023.4.6 definitely introduce…

Some behavior in HA Core itself must have changed, as this was working for (more than 2 years) without any issues.

---

Update: here’s long-term data from my InfluxDB which actually and definitely confirms HA Core update 2023.4.6 screwed this up:

• mean values
  
  
• actual values
  
  

---

I asked at Negative Values in Energy Dashboard since last update - #104 by e-raser if it really was introduced with HA Core 2023.5.x and only for Riemann Sum integration. I suspect it was introduced earlier (in some 2023.4.x release) and also affects the utility meter integration.

Because: all my used input sensors come from a utility meter! And if the input sensors of those utility meters are unavailable, the utility meter should just ignore/skip those values and keep the last one!

For now I will

1. likely not fix this in the /developer-tools/statistics section but
2. try to add a proper availability_template to suppress this issue and
3. clarify which HA Core release fixed this and urgently update to that one

[Edwin_D]

The fact that you didn’t have this problem without availability template is luck. Also the behavior change could be from the integration that provides the three subsensors.

I must admit I too had more problems like these from my water sensor around the same time. They also lacked an availability template, so indeed something changed in HA for the issue to become more apparent. I suspect the statistics start working earlier during the boot process, when not all integrations are done loading.

But I would not classify it a bug. During reboot sensors do become unavailable (you may miss events in that period) so it is recording the truth.

Availability templates are a crucial construct that is often overlooked, also here on the forum. Most people rely on float(0) to set the value to 0 for unavailable, which is often plain wrong.

The conclusion that only Riemann sum has problems with sensors that are not reporting availability correctly is wrong, it is just apparent there. Any sensor defaulting to a numerical value for unavailable can cause problems if the default is different from expected.

Maybe this is a good time to vote for my feature request to add availability templates to the gui for the people who are not yaml experts. I think I created it around the time my water sensor started doing similar things:

Add availability template to the new template sensor helper

It is great that template sensors can now be added through the UI. However, for numerical sensors it is often very important to add an availability template. If you do not, often the template will revert to returning 0 values that will result in a mess when the template sensor is later used in derivatives, integrals, energy dashboards etc.

[e-raser]

Don’t want to double post, therefore refering to

Negative Values in Energy Dashboard since last update

Likely a good thing to work with availability_template, which is what I did yesterday. raw input sensor → utility meter sensor → template sensor BUT as the input sensor for my affected template sensors are from a utility meter… that integration should know about how to handle unavailable its own input sensors the right way still something has changed in HA! Before (in my case) HA Core 2023.4.x there was no need to suppress unavailable states, this was handled bullet-broof by HA internally…

Indeed: “Riemann sum” integration is only one part of the puzzle, utility meter integration (that’s where I encountered the issue) is another one.

It’s freaking me out no one over at Negative Values in Energy Dashboard since last update seems to be able to specify which HA Core version fixed this (or if it is fixed at all without manually adding availability_tempates). Up to now I don’t even know if using availability_tempate efficiently avoids such data garbage in future - I will know in a few days.

By the way this is one example what my availability_templates look like:

        availability_template: >-
          {{
            states('sensor.energy_1_total') not in ['NULL', 'unavailable', 'unknown', 'none'] and
            states('sensor.energy_2_total') not in ['NULL', 'unavailable', 'unknown', 'none'] and
            states('sensor.energy_3_total') not in ['NULL', 'unavailable', 'unknown', 'none'] and
            ...
          }}

(important to not filter for 0 because that is a valid state of my utility meter input sensors: at the beginning of a day/month/year etc. their state is 0 as long as kWh is recorded)

Anyway, feels like a suppressing plaster instead of fixing the original issue while at the same time still not knowing what exactly introduced this. Not to speak from a somehow “official” statement of the HA dev core team.