Statistics Explorer with Apex Chart

I was very happy when long term statistics were introduced, however I am a little underwhelmed by the possibilities to explore, browse und visualize this data. Neither the statistics_graph card nor the energy-dashboard have a lot of flexibility.

Inspired by several posts here, especially Dynamically select the number of hours or sensors to show on a history graph or mini graph card i tried to to create the “statistics explorer”.


You select an entity, a timespan and a date, and the apex-chart will show data for the selected timespan that contains the selected date, much like the energy-dashboard (Month: 1 - 28/29/30/31; week: monday - sunday; year: 01.01 - 31.12).
You can use the arrows to jump forward or backwards or to return to today.

ToDo:

  • Apex seems to have problems to aggregate data on a month-level, so timespan-year is aggregated on a daily level for now.
  • Clean up the form. Right now I can’t get it to work without vertical-stack,which leaves a nasty border.
  • create some nicer charts

Prerequisites:
ApexCharts
Card-Mod
Config Template Card

INPUTS:

input_select:
  statistics_chart_entity:
    initial: sensor.bathroom_meter_boiler_energy
    options:
      - sensor.balkon_light_main_energy
      - sensor.bathroom_skylight_energy
      - sensor.bathroom_meter_boiler_energy
      - sensor.hall_outlet_network_energy
      - sensor.hall_skylight_energy
      - sensor.home_calc_base_energy
      - sensor.home_calc_correction_energy
      - sensor.home_calc_light_energy
      - sensor.home_calc_misc_energy
      - sensor.home_calc_unknown_energy
      - sensor.home_meter_grid_energy
      - sensor.home_outlet_float_energy
      - sensor.home_outlet_vacuum_energy
      - sensor.kitchen_meter_stove_energy
      - sensor.kitchen_outlet_boiler_energy
      - sensor.kitchen_outlet_dishwasher_energy
      - sensor.kitchen_outlet_fridge_energy
      - sensor.livingroom_light_counter_energy
      - sensor.livingroom_outlet_media_energy
      - sensor.livingroom_outlet_fan_energy

  statistics_chart_range:
    initial: month
    options:
      - day
      - week
      - month
      - year

input_datetime:
  statistics_chart_date:
    has_date: true
    has_time: false

Templates:

template:
  - sensor:
      - name: statistics_chart_grouping
        unique_id: statistics_chart_grouping
        state: |
          {% set grouping={
            'day':'h',
            'week':'d',
            'month':'d',
            'year':'d'}
          %}
          {% set range = states('input_select.statistics_chart_range') %}
          {{ grouping[range] }}
      - name: statistics_chart_span
        unique_id: statistics_chart_span
        state: |
          {% macro last_date_in_range(date_str,range) -%}
            {% set date=as_timestamp(date_str)|timestamp_local()|as_datetime() -%}
            {% if range=='day' -%}
              {% set end_date=date -%}
            {% elif range=='week' -%}
              {% set end_date=date + timedelta(days=(6-date.weekday())) -%}
            {% elif range=='month' -%}
              {% set month=(date.month % 12) + 1 -%}
              {% set year=date.year+iif(date.month==12,1,0) -%}
              {% set end_date=strptime(year|string + '-' + month|string + '-01', '%Y-%m-%d')-timedelta(days=1) -%}
            {% elif range=='year' -%}
              {% set end_date=strptime(date.year|string + '-12-31', '%Y-%m-%d') -%}
            {% endif -%}
            {{ as_timestamp(end_date)|timestamp_local() -}}
          {% endmacro -%}
          {% set range = states('input_select.statistics_chart_range') %}
          {% set date_str = states('input_datetime.statistics_chart_date') %}
          {% set spans={
            'day':'24',
            'week':'7',
            'month':(last_date_in_range (strptime(date_str, '%Y-%m-%d'),range)|as_datetime()).day,
            'year':last_date_in_range (strptime(date_str, '%Y-%m-%d'),range)|as_timestamp(0)|timestamp_custom('%j',0)|int +1}
          %}
          {{ spans[range] }}
      - name: statistics_chart_period
        unique_id: statistics_chart_period
        state: |
          {% set periods={
            'day':'hour',
            'week':'day',
            'month':'day',
            'year':'day'}
          %}
          {% set range = states('input_select.statistics_chart_range') %}
          {{ periods[range] }}
  - sensor:
      - name: statistics_chart_offset
        unique_id: statistics_chart_offset
        state: |
          {% macro last_date_in_range(date_str,range) -%}
            {% set date=as_timestamp(date_str)|timestamp_local()|as_datetime() -%}
            {% if range=='day' -%}
              {% set end_date = date + timedelta(days=1) -%}
            {% elif range=='week' -%}
              {% set end_date=date + timedelta(days=(6-date.weekday())) -%}
            {% elif range=='month' -%}
              {% set month=(date.month % 12) + 1 -%}
              {% set year=date.year+iif(date.month==12,1,0) -%}
              {% set end_date=strptime(year|string + '-' + month|string + '-01', '%Y-%m-%d')-timedelta(days=1) -%}
            {% elif range=='year' -%}
              {% set end_date=strptime(date.year|string + '-12-31', '%Y-%m-%d') -%}
            {% endif -%}
            {{ as_timestamp(end_date) -}}
          {% endmacro -%}
          {% set range = states('input_select.statistics_chart_range') %}
          {% set date_str = states('input_datetime.statistics_chart_date') %}
          {% set offset = last_date_in_range(strptime(date_str, '%Y-%m-%d'),range)|int - now()|as_timestamp(0) -%}
          {% if states('sensor.statistics_chart_grouping') == 'h' %}
            {% set offset=(offset/60/60)|round(method='ceil') %}
          {% else %}
            {% set offset=(offset/60/60/24)|round(method='ceil') + 1 %}
          {% endif %}
          {{ iif(offset >= 0,'+','') }}{{ offset }}{{ states('sensor.statistics_chart_grouping') }}

Scripts:

script:
  statistics_chart_date_browse:
    icon: mdi:arrow-right-bold-outline
    sequence:
      - service: input_datetime.set_datetime
        entity_id: input_datetime.statistics_chart_date
        data:
          datetime: |
            {% set range = states('input_select.statistics_chart_range') %}
            {% set date = states('input_datetime.statistics_chart_date')|as_datetime %}
            {% set direction = direction %}
            {% if range == 'day' %}
              {% if direction > 0 %}
                {% set target = date + timedelta(days=1) %}
              {% else %}
                {% set target = date - timedelta(days=1) %}
              {% endif %}
            {% elif range == 'week' -%}
              {% if direction > 0 %}
                {% set target = date + timedelta(days=7) %}
              {% else %}
                {% set target = date - timedelta(days=7) %}
              {% endif %}
            {% elif range == 'month' -%}
              {% if direction > 0 %}
                {% set month=((date.month) % 12) + 1 %}
                {% set year=date.year+iif(date.month==12,1,0) %}
                {% set target=strptime(year|string + '-' + month|string + '-01', '%Y-%m-%d') -%}
              {% else %}
                {% set month=((date.month - 2) % 12) + 1 %}
                {% set year=date.year+iif(date.month==1,-1,0) %}
                {% set target=strptime(year|string + '-' + month|string + '-01', '%Y-%m-%d') -%}
              {% endif %}
            {% elif range=='year' -%}
              {% if direction > 0 %}
                {% set target=strptime((date.year|int + 1)|string + '-01-01', '%Y-%m-%d') -%}
              {% else %}
                {% set target=strptime((date.year|int - 1)|string + '-01-01', '%Y-%m-%d') -%}
              {% endif %}
            {% endif -%}
            {% if direction == 0 %}
                {% set target = states('sensor.date') %}
            {% endif -%}
            {{ target }}

Dashboard-card inputs:

type: custom:mod-card
card_mod:
  style:
    hui-entities-card:
      $: |
        :host {
          flex: auto !important;
          width: 70%;
          padding: 0px;
          margin: 0px !important;
        }
        div#states {
          margin: 0px;
          padding: 7px
        }
      $hui-input-select-entity-row:
        $hui-generic-entity-row:
          $: |
            state-badge {
              display: none;
            }
          ha-select$: |
            span#label {
            }
card:
  type: entities
  entities:
    - entity: input_select.statistics_chart_entity
      name: entity
type: custom:mod-card
card_mod:
  style:
    hui-horizontal-stack-card:
      .: |
        ha-card {
          height: 100%;
          padding: 0px;
          margin: 0px;
        }
      $: |
        hui-entities-card:nth-of-type(1){
          width: 60%;
        }
        hui-entities-card:nth-of-type(2){
          width: 40%;
        }
      $hui-entities-card:
        $: |
          :host {
            flex: auto !important;
            width: 70%;
            padding: 0px;
            margin: 0px !important;
          }
          div#states {
            margin: 0px;
            padding: 7px
          }
        $hui-input-select-entity-row:
          $hui-generic-entity-row:
            $: |
              state-badge {
                display: none;
              }
            ha-select$: |
              span#label {
              }
        $hui-input-datetime-entity-row:
          $hui-generic-entity-row:
            $: |
              state-badge {
                display: none;
              }
              div {
                display: none;
              }
            ha-date-input$: |
              ha-textfield, :host {
                width: 100%;
                border: 0px;
              }
      $hui-glance-card:
        $: |
          :host {
            flex: auto !important;
              --glance-column-width: 100px;
            padding: 0px;
            margin: 0px !important;
            }        
          div.entities.no-header {
            margin: 0px;
            padding: 7px
          }
          div.entity {
            background: var(--mdc-select-fill-color, whitesmoke);
            margin: 0px;
            padding: 0px;
          }
        $div.entity:
          $: |
            state-badge {
              color: <var(--primary-text-color);
            }
card:
  type: horizontal-stack
  cards:
    - type: entities
      entities:
        - entity: input_datetime.statistics_chart_date
          name: date
    - type: entities
      entities:
        - entity: input_select.statistics_chart_range
          name: period
    - type: glance
      show_name: false
      show_state: false
      entities:
        - entity: script.statistics_chart_date_browse
          icon: mdi:chevron-left
          tap_action:
            action: call-service
            service: script.statistics_chart_date_browse
            data:
              direction: -1
        - entity: script.statistics_chart_date_browse
          icon: mdi:chevron-down
          tap_action:
            action: call-service
            service: script.statistics_chart_date_browse
            data:
              direction: 0
        - entity: script.statistics_chart_date_browse
          icon: mdi:chevron-right
          tap_action:
            action: call-service
            service: script.statistics_chart_date_browse
            data:
              direction: 1

Dashboard-card: graph:

type: custom:config-template-card
variables:
  entity: states['input_select.statistics_chart_entity'].state
  offset: states['sensor.statistics_chart_offset'].state
  span: states['sensor.statistics_chart_span'].state
  period: states['sensor.statistics_chart_period'].state
  grouping: states['sensor.statistics_chart_grouping'].state
entities:
  - input_select.statistics_chart_entity
  - sensor.statistics_chart_offset
  - sensor.statistics_chart_span
  - sensor.statistics_chart_period
  - sensor.statistics_chart_grouping
card:
  type: custom:apexcharts-card
  header:
    show: true
    title: Statistics
    show_states: false
    colorize_states: true
  apex_config:
    chart:
      zoom:
        enabled: true
      toolbar:
        show: false
        tools:
          zoom: true
          zoomin: true
          zoomout: true
          pan: true
          reset: true
  graph_span: ${span + period}
  span:
    offset: ${offset}
  yaxis:
    - min: 0
      decimals: 2
  stacked: false
  all_series_config:
    type: column
    statistics:
      type: sum
      period: ${period}
      align: end
    group_by:
      func: diff
      duration: ${1 + grouping}
      fill: last
      start_with_last: true
    float_precision: 2
    time_delta: +0d
    show:
      legend_value: false
  series:
    - entity: ${entity}

The templates and cards might be overcomplicated, but I couldn’t manage another way. I’m open for improvements.

The recent improvements to the database make this very fast, even on my RPi 4.

[EDIT: 2022-06-19] updated the definitions of the cards for the inputs, especially the styling.

[EDIT: 2022-06-22] Added the config Template Card to the prerequisites. I somehow forgot :thinking:

14 Likes

Thanks looks interesting might try this when I get on my laptop.
I need to convert my energy sensors as most of them aren’t set up for long term statistics.
I use the utility meter integration which gives me peak and off peak values and then I have a template sensor that adds both together to give me the overall amount, and then another template that shows the overall cost.
So I have about 6 sensors all together showing daily/weekly & monthly usage and cost but these which I would like in something like this.
Does your template take into account the leap year in February ? I can’t tell

As all the date calculations are done with a datetime object it does.

Based on the entities (inputs, templates, scripts) from the first post, here are some cards targeted at analyzing the energy-consumption:

The first chart will shows the portion of each consumer of the total energy consumption in the selected range containing the selected date.

When you click the consumption in the header it will update the selected entity input and show the timeline and the total of the selected entity in the selected range in the bar-chart.
You can also select an entity directly in the input_select, that might not be in the bar-chart, to show its timeline.

Be warned: depending on the selected range and the amount of entities, updates of the pie-chart might take a while :thinking:

Pie-chart:

type: custom:config-template-card
variables:
  range: states['input_select.statistics_chart_range'].state
  offset: states['sensor.statistics_chart_offset'].state
  span: states['sensor.statistics_chart_span'].state
  period: states['sensor.statistics_chart_period'].state
  grouping: states['sensor.statistics_chart_grouping'].state
entities:
  - input_select.statistics_chart_range
  - sensor.statistics_chart_offset
  - sensor.statistics_chart_span
  - sensor.statistics_chart_period
  - sensor.statistics_chart_grouping
card:
  type: custom:apexcharts-card
  header:
    show: true
    title: Distribution
    show_states: true
    colorize_states: true
  apex_config:
    legend:
      show: false
    chart:
      zoom:
        enabled: true
      toolbar:
        show: false
        tools:
          zoom: true
          zoomin: true
          zoomout: true
          pan: true
          reset: true
  graph_span: ${span + period}
  chart_type: pie
  yaxis:
    - id: main
      min: 0
      decimals: 2
    - id: none
      show: false
      min: 0
      decimals: 1
  span:
    offset: ${offset}
  all_series_config:
    type: column
    yaxis_id: main
    statistics:
      type: sum
      period: ${period}
      align: end
    group_by:
      func: diff
      duration: ${1 + range}
      fill: last
      start_with_last: true
    float_precision: 2
    time_delta: +0d
    show:
      legend_value: false
      datalabels: percent
      in_header: true
  series:
    - entity: sensor.home_meter_grid_energy
      name: Total
      color: black
      yaxis_id: none
      show:
        in_chart: false
        in_header: true
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.home_meter_grid_energy
    - entity: sensor.bathroom_meter_boiler_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.bathroom_meter_boiler_energy
    - entity: sensor.hall_outlet_network_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.hall_outlet_network_energy
    - entity: sensor.home_calc_light_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.home_calc_light_energy
    - entity: sensor.home_calc_misc_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.home_calc_misc_energy
    - entity: sensor.home_calc_unknown_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.home_calc_unknown_energy
    - entity: sensor.kitchen_meter_stove_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.kitchen_meter_stove_energy
    - entity: sensor.kitchen_outlet_boiler_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.kitchen_outlet_boiler_energy
    - entity: sensor.kitchen_outlet_fridge_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.kitchen_outlet_fridge_energy
    - entity: sensor.livingroom_outlet_media_energy
      header_actions:
        tap_action:
          action: call-service
          service: input_select.select_option
          service_data:
            entity_id: input_select.statistics_chart_entity
            option: sensor.livingroom_outlet_media_energy

Bar-Chart:

type: custom:config-template-card
variables:
  entity: states['input_select.statistics_chart_entity'].state
  range: states['input_select.statistics_chart_range'].state
  offset: states['sensor.statistics_chart_offset'].state
  span: states['sensor.statistics_chart_span'].state
  period: states['sensor.statistics_chart_period'].state
  grouping: states['sensor.statistics_chart_grouping'].state
entities:
  - input_select.statistics_chart_entity
  - input_select.statistics_chart_range
  - sensor.statistics_chart_offset
  - sensor.statistics_chart_span
  - sensor.statistics_chart_period
  - sensor.statistics_chart_grouping
card:
  type: custom:apexcharts-card
  header:
    show: true
    show_states: true
    colorize_states: false
  apex_config:
    chart:
      zoom:
        enabled: true
      toolbar:
        show: false
        tools:
          zoom: true
          zoomin: true
          zoomout: true
          pan: true
          reset: true
  graph_span: ${span + period}
  span:
    offset: ${offset}
  yaxis:
    - id: main
      min: 0
      decimals: 2
    - id: none
      show: false
      min: 0
      decimals: 2
  stacked: false
  all_series_config:
    type: column
    statistics:
      type: sum
      period: ${period}
      align: end
    group_by:
      func: diff
      duration: ${1 + grouping}
      fill: last
      start_with_last: true
    float_precision: 2
    time_delta: +0d
    show:
      legend_value: false
      in_header: false
  series:
    - entity: ${entity}
      yaxis_id: main
    - entity: ${entity}
      name: Total
      yaxis_id: none
      group_by:
        duration: ${1 + range}
      show:
        in_chart: false
        in_header: true

3 Likes

Have you tried using the statistics option instead of using the group by ?
From what I’ve read on the main apex chart page that takes the the data from the long term statistics so displays on the chart straight away where as the group by option calculates it so that’s why it takes longer to populate.
I tried it on a couple of smaller bar charts.

I’m not sure, what you mean. I am using the statistics, that’s what this is all about.

As far as I understand, the goup_by option is about aggregating data not about the source of the data.

Without the group_by it would not be possible to display consumption by range, as this is an ever increasing value.

Or did I miss something?

Maybe it’s me getting confused then.
Soi use the utility meter integration that gives me the daily, weekly and monthly energy sensors.
If you then create a template for each sensor so it has he state_class: total_increasing and device_class it will be included in HA’s long term statistics.
So on the apex card you don’t use the group by you add statistics like below and from what I’ve read on the main apex page the apex charts it won’t need to calculate any data so the charts load straight away.
I’m really not sure if you can use it for the advanced charts you are doing just thought I’d mention it in case you could.

type: custom:apexcharts-card
graph_span: 7d
update_interval: 5min
apex_config:
  dataLabels:
    enabled: true
    dropShadow:
      enabled: true
  plotOptions:
    bar:
      borderRadius: 1
      columnWidth: 80%
      dataLabels:
        style:
          fontSize: 12px
        background:
          enabled: true
        position: top
        enabled: true
  xaxis:
    labels:
      format: ddd
  stroke:
    show: falsetrue
    width: 1.5
    curve: smooth
  chart:
    height: 180%
  legend:
    show: true
    showForSingleSeries: true
    horizontalAlign: right
    position: top
  fill:
    type: gradient
    gradient:
      type: horizontal
      shadeIntensity: 0...9
      opacityFrom: 0.5
      opacityTo: 0.9
      inverseColors: true
      stops:
        - 0
        - 90
        - 100
header:
  show: true
  title: Daily Energy
series:
  - entity: sensor.daily_energy
    name: Stats
    type: column
    color: fc7703
    float_precision: 1
    statistics:
      type: state
      period: day
      align: end

Ah, now I get it. Makes sense.
I don’t use utility meters, as I don’t see the point when there are statistics.
I might reconsider.

I thought about this but I don’t think this will help.
These chart use several different groupings on different ranges (hour, day, month). This would mean I would need several utility meters for each entity and would have to introduce additional logic to select the right utility meter depending on the selected range and entity.

Is this still an issue?
I can’t get the month-level to work.

Hi, Great work. Thanks. I have been using this for months.

I have a problem (maybe it is by design) with the cutoff timing for each bar. The bars starting time are based on the current system time. Is it possible to make it fixed at the beginning of the hour?

I tried to play with the offset but seems like when the period is ‘day’ the offset defaults to hour without minutes.

Any idea? Thanks