Compare current data with historical data over a period of time (e.g. today with yesterday, same day previous year)

[Steph-74]

Hi,
my use case is as foloows: I would like to compare my electricity consumption from today with data from the past in order to see, how certain changes (i.e. installation of photovoltaics, replacing the ICE with a BEV ... and next year the heat pump) affect the electricity consumption. To get the electricity consumption I add/substract a couple of entities. With the help of plotly and some discussions here I actually came petty far:

I will focus now on the self-consumption, as getting the overall electricity consumption is only on more entity to add. The above chart is generated with following coding:

type: custom:plotly-graph
title: Eigenverbrauch
hours_to_show: current_day
config:
  displayModeBar: false
layout:
  dragmode: false
  legend:
    orientation: h
    itemsizing: constant
    font:
      size: 8
  xaxis:
    visible: true
    fixedrange: true
  yaxis:
    visible: true
    fixedrange: true
    rangemode: tozero
    title: Wh
  yaxis9:
    visible: false
    fixedrange: true
entities:
  - entity: sensor.total_battery_energy_charged
    internal: true
    statistic: sum
    period: 5minute
    filters:
      - derivate
      - integrate: null
      - store_var: to_battery
  - entity: sensor.total_battery_energy_discharged
    internal: true
    statistic: sum
    period: 5minute
    filters:
      - derivate
      - integrate: null
      - store_var: from_battery
  - entity: sensor.smart_meter_ts_65a_3_energy_real_produced
    internal: true
    statistic: sum
    period: 5minute
    filters:
      - derivate
      - integrate: null
      - store_var: to_grid
  - entity: sensor.wattpilot_totally_charged
    internal: true
    statistic: sum
    period: 5minute
    filters:
      - derivate
      - integrate: null
      - store_var: to_car
  - entity: sensor.total_photovoltaics_energy
    name: heute
    statistic: sum
    period: 5minute
    filters:
      - derivate
      - integrate: null
      - map_y: >-
          parseFloat(ys[i]) - parseFloat(vars.to_grid.ys[i]) -
          parseFloat(vars.to_battery.ys[i]) +
          parseFloat(vars.from_battery.ys[i]) - parseFloat(vars.to_car.ys[i])
    line:
      color: rgba(255, 0, 0, 1)
      width: 2
      shape: spline
    show_value: true
    texttemplate: <b> %{y:.0f} Wh</b>
    hovertemplate: |
      <i>Heute</i><br>
      <i>%{x}</i><br>
      <b>%{y:.0f} Wh</b>
      <extra></extra>
  - entity: sensor.total_battery_energy_charged
    internal: true
    statistic: sum
    period: 5minute
    time_offset: +1d
    filters:
      - derivate
      - integrate: null
      - store_var: to_battery_1
  - entity: sensor.total_battery_energy_discharged
    internal: true
    statistic: sum
    period: 5minute
    time_offset: +1d
    filters:
      - derivate
      - integrate: null
      - store_var: from_battery_1
  - entity: sensor.smart_meter_ts_65a_3_energy_real_produced
    internal: true
    statistic: sum
    period: 5minute
    time_offset: +1d
    filters:
      - derivate
      - integrate: null
      - store_var: to_grid_1
  - entity: sensor.wattpilot_totally_charged
    internal: true
    statistic: sum
    period: 5minute
    time_offset: +1d
    filters:
      - derivate
      - integrate: null
      - store_var: to_car_1
  - entity: sensor.total_photovoltaics_energy
    internal: true
    statistic: sum
    period: 5minute
    time_offset: +1d
    filters:
      - derivate
      - integrate: null
      - store_var: prod_1
  - entity: ''
    name: '-1 Tag'
    time_offset: +1d
    filters:
      - load_var: prod_1
      - map_y: >-
          parseFloat(vars.from_battery_1.ys[i]) + parseFloat(vars.prod_1.ys[i])
          -  parseFloat(vars.to_battery_1.ys[i]) -
          parseFloat(vars.to_grid_1.ys[i]) - parseFloat(vars.to_car_1.ys[i])
    line:
      color: rgba(52, 152, 219, .5)
      width: 1
      shape: spline
    show_value: true
    texttemplate: ' %{y:.0f} Wh'
    hovertemplate: |
      <i>Gestern</i><br>
      <b>%{y:.0f} Wh</b>
      <extra></extra>
  - entity: sensor.total_battery_energy_charged
    internal: true
    statistic: sum
    filters:
      - derivate
      - integrate:
          reset_every: 1d
      - store_var: to_battery_7
    time_offset: +7d
  - entity: sensor.total_battery_energy_discharged
    internal: true
    statistic: sum
    filters:
      - derivate
      - integrate:
          reset_every: 1d
      - store_var: from_battery_7
    time_offset: +7d
  - entity: sensor.smart_meter_ts_65a_3_energy_real_produced
    internal: true
    statistic: sum
    filters:
      - derivate
      - integrate:
          reset_every: 1d
      - store_var: to_grid_7
    time_offset: +7d
  - entity: sensor.wattpilot_totally_charged
    internal: true
    statistic: sum
    filters:
      - derivate
      - integrate:
          reset_every: 1d
      - store_var: to_car_7
    time_offset: +7d
  - entity: sensor.total_photovoltaics_energy
    internal: true
    statistic: sum
    filters:
      - derivate
      - integrate:
          reset_every: 1d
      - store_var: prod_7
    time_offset: +7d
  - entity: ''
    name: '-7 Tage'
    time_offset: +7d
    filters:
      - load_var: prod_7
      - map_y: >-
          parseFloat(vars.from_battery_7.ys[i]) + parseFloat(vars.prod_7.ys[i])
          -        parseFloat(vars.to_battery_7.ys[i]) - 
          parseFloat(vars.to_grid_7.ys[i]) - parseFloat(vars.to_car_7.ys[i])
    line:
      color: rgba(155, 89, 182, .5)
      width: 1
      shape: spline
    show_value: true
    texttemplate: ' %{y:.0f} Wh'
    hovertemplate: |
      <i>letzte Woche</i><br>
      <b>%{y:.0f} Wh</b>
      <extra></extra>
  - entity: ''
    name: Now
    yaxis: y9
    showlegend: false
    line:
      width: 1
      dash: dot
      color: deepskyblue
    x: $fn () => [Date.now(), Date.now()]
    'y': $fn () => [0,1]

As I dug into the source data (SQLite and Statisics Card), I discovered that I need to select the last record from the previous day in order to get the starting point for the sum correct. Therfore I need a function, but all my trials failed.

... apart from that, the vars data in the debuger also looks a bit wired - but compared to my intial post concerning this I can now recreate the number plotly is calculating and think, it might be related as I do not have the correct starting point:

I highly appreciate any help to get this correct ... thanks in advance.

[dbuezas]

Hey @Steph-74 !
That's a cool use case, I see you're diving deep into this card 💪

I can't test this right now, but I have some suspicions:

1. I see you are applying derivative and integration to all entities before storing them in variables. That shouldn't be necessary and it creates a NaN value at the beginning. This NaN occurs because the derivative is (current_value - previous_value)/time_difference, and for the first element, previous_value is undefined. Just remove those from the filters list and leave the store_var
2. The second NaN should be because you also use the derivate and integrate with daily reset trick, so it will be hard to get rid off. Maybe you could add a small global time_offset so the card fetches a bit of data from the previous day, this way you get an extra value in the past that can be safely discarded.
3. Another option is to use the state statistic (instead of sum) and then using integrate without derivating first.
4. If the data is still wrong, try searching for reset in the discussions and issues, there may be a custom filter there that works without having to derivate.

Let me know how it goes!

If nothing works we can try creating a custom filter together, I'm on my phone so it will need a couple of tries.

PS: I think I got a donation from you, thank you! I'll put it to good use in a mixture of beer and supporting other projects :)

[dbuezas]

Oh really? That's a bug then!
Can you append a 0 value to the entities that don't have it to confirm this would fix your issue?

filters:
  - fn: |
     ({xs, ys}) => {
         xs.shift(new Date("2023-01-01"));
         ys shift(0);
         return {xs, ys};
     }

[Steph-74]

Hi,
did not fix it with the shift-code block, but it pointed me into another direction: I added following codeblock with an unshift to the entities that do not start at the beginning of the reporting period:

      - fn: |
          ({xs, ys}) => {
              xs.unshift(new Date("2023-12-31 23:00:00")); 
              ys.unshift(0); 
              return {xs, ys};
          }

This fixed (more or less) the issue, the chart shall look like this, also the month total (purple number) is correct:

However, the array length does not match 100 % (743 vs. 742).

[dbuezas]

Oh, sorry I always confuse shift and unshift.

That's probably because the other entities start one hour later or before. Just add one hour to the new Date

[Steph-74]

No worries ... I am rather proud of myself when I figure these things out on my own as my programming skills tend to zero ;)
The "new Date" part behaves rather strangely, i.e. when adding one hour, the array has 2 lines less and I end up with 741 vs. 742. However, the visulization works for me now.

You mentioned earlier that the resample might be buggy, when the trace itself starts sometime within the reporting time frame. Shall I open an issue for that?

[dbuezas]

Yes please. The point of resample is almost always to align entities so they not starting at the same time is an issue.
I just thought of something else you could do: reverse the arrays so they go backwards when adding them together