ApexCharts card - A highly customizable graph card

davo · January 27, 2025, 3:52am

Hi, I have a problem with y-axis labels not showing correctly.
The data is stored in an influx database.

Below is the data from influx for hotwater usage per year.

2021-12-31T23:59:59+10:00 year 277.96
2022-12-31T23:59:59+10:00 year 493.5
2023-12-31T23:59:59+10:00 year 598.34
2024-12-31T23:59:59+10:00 year 760.15
2025-12-31T23:59:59+10:00 year 33.17

Epoch time:

1640959199000000000 year 277.96
1672495199000000000 year 493.5
1704031199000000000 year 598.34
1735653599000000000 year 760.15
1767189599000000000 year 33.17

Code for card:

type: custom:apexcharts-card
header:
  show: true
  title: Hotwater Power Usage - Yearly
  show_states: false
  colorize_states: true
apex_config:
  chart:
    height: 440
    extend_to: now
  xaxis:
    type: datetime
    labels:
      show: true
      rotate: 0
  yaxis:
    min: 0
    forceNiceScale: true
  stroke:
    show: true
    width: 1
  legend:
    show: true
graph_span: 10year
span:
  end: year
series:
  - entity: sensor.influxdb_read
    type: column
    float_precision: 2
    name: Yearly Usage kW
    group_by:
      func: raw
    color: '#acd373'
    transform: return x / 1024;
    data_generator: |
      var params = new URLSearchParams({
          db: "sensors",
          q: "SELECT \"value\" FROM \"hotwater\" WHERE \"entity_id\" = 'year'"
          });

      var myInit = { method: 'GET',
                      headers: {
                          'Accept': 'application/json',
                      },
                      mode: 'cors',
                      cache: 'default' 
      };

      const request = async () => {
          var result = [];
          const response = await fetch('http://pi5:8086/query?' + params, myInit)
          const json = await response.json();
          if (json["results"] && json["results"][0] && json["results"][0]["series"]  && json["results"][0]["series"][0] && json["results"][0]["series"][0]["values"]) {
              for(var val of json["results"][0]["series"][0]["values"]) {
                  result.push([new Date(val[0]), val[1]]);
              }
          } 
          return result;
      }
      return request();
update_interval: 5m

Which produces the below chart, however the dates on Y-Axis are wrong. 2026 has 2025 data on it. The data us correct.

If I change group_by to:

group_by:
      func: last
      duration: 1year

The labels on Y-Axis are correct, however some of the data is wrong. Eg: 2024 should be 760.15kw but shows 598.34kW which is what 2023 is. It also shows 598.34kW for 2023, which is correct. The time is also wrong, They should all be 31 of dec and 23:59:59.

Is there something I can do to fix this or is it a bugs ?
Thanks

davo · January 27, 2025, 4:01am

You can write the data to influx, then read it back.
I have a table for daily, monthly and yearly data in the database - it looks like this for yearly:

root@pi5:~# influx -database sensors -execute "SELECT * FROM hotwater WHERE entity_id='year'"
name: hotwater
time                entity_id value
----                --------- -----
1640959199000000000 year      277.96
1672495199000000000 year      493.5
1704031199000000000 year      598.34
1735653599000000000 year      760.15
1767189599000000000 year      33.17

configuration.yaml

influxdb:
  host: 127.0.0.1
  database: sensors
  default_measurement: value
  measurement_attr: entity_id
  max_retries: 3
  exclude:
    entity_globs: "*"

shell_command:
    influxdb_update: "curl -H 'Content-Type: application/json' -i -XPOST '{{ url }}' --data-binary '{{ data }}'"

automations.yaml

  - alias: hotwater_data_save
    description: "save DDS hotwater cylinder message for energy usage"
    trigger:
    - platform: mqtt
      topic: 'tele/tasmota_88D1BE/SENSOR'
    action:
      - service: shell_command.influxdb_update
        data:
          url: 'http://localhost:8086/write?db=sensors'
          data: 'hotwater,entity_id=day value={{states("sensor.hotwater_today")}} {{ (as_timestamp(now().strftime("%Y-%m-%dT23:59:59")) | round(0,default = 0) ) * 1000000000  }}'
      - service: shell_command.influxdb_update
        data:
          url: 'http://localhost:8086/write?db=sensors'
          data: 'hotwater,entity_id=month value={{ states("sensor.hotwater_month") }} {{ (as_timestamp(now().replace(day=31 if now().month == 12 else (now().replace(month=now().month+1, day=1) - timedelta(days=1)).day).replace(hour=23).replace(minute=59).replace(second=59).replace(microsecond=0)) | round(0,default = 0) ) * 1000000000 }}'
      - service: shell_command.influxdb_update
        data:
          url: 'http://localhost:8086/write?db=sensors'
          data: 'hotwater,entity_id=year value={{ states("sensor.hotwater_year") }} {{ (as_timestamp(now().strftime("%Y-12-31T23:59:59")) | round(0,default = 0) ) * 1000000000  }}'

The above code writes to the influx database whenever mqtt data is posted form sensor tele/tasmota_88D1BE/SENSOR which is a meter on my hotwater cylinder.

And the code for the apexcharts card to get it from influx into the chart for monthly data:

type: custom:apexcharts-card
header:
  show: true
  title: Hotwater Power Usage - Monthly
  show_states: false
  colorize_states: true
apex_config:
  chart:
    height: 445
    extend_to: now
  yaxis:
    min: 0
    max: 80
    forceNiceScale: true
  stroke:
    show: true
    width: 1
  legend:
    show: true
graph_span: 1year
span:
  end: month
series:
  - entity: sensor.influxdb_read
    type: column
    name: Monthly Usage kW
    color: '#acd373'
    data_generator: |
      var params = new URLSearchParams({
          db: "sensors",
          q: "SELECT \"value\" FROM \"hotwater\" WHERE \"entity_id\" = 'month'"
          });

      var myInit = { method: 'GET',
                      headers: {
                          'Accept': 'application/json',
                      },
                      mode: 'cors',
                      cache: 'default' 
      };

      const request = async () => {
          var result = [];
          const response = await fetch('http://pi5:8086/query?' + params, myInit)
          const json = await response.json();
          if (json["results"] && json["results"][0] && json["results"][0]["series"]  && json["results"][0]["series"][0] && json["results"][0]["series"][0]["values"]) {
              for(var val of json["results"][0]["series"][0]["values"]) {
                  result.push([new Date(val[0]), val[1]]);
              }
          } 
          return result;
      }
      return request();
update_interval: 5m

I have 3 sensors for each item I want to save data for - day, month, year, and these, just adding the daily data to monthly and yearly sensors. At the end of each day I reset the daily sensor to zero, end of month monthly sensor to zero… I save data to the influx database, so I can chart daily, monthly and yearly usage of power for hotwater, or solar production, or grid power usage etc…

Hope this helps

vingerha · January 27, 2025, 8:03am

Not easy to see where things go wrong, what you post is a influx datalist but what your code pulls out…no clue. Try offsetting the lot with 1 day (or a few hours)?

davo · January 27, 2025, 8:52am

I duplicated below post somehow.

davo · January 27, 2025, 9:06am

Hi @vingerha
I am guessing the data pulled out is correct, because with the func: raw the data is correct.

If I offset by at least +150d the y axis labels now line up, however it now has wrong date when cursor is over the column as shown in below screen capture:

Would home assistant be caching the data ?

I have clear browser cache and have latest version of apex charts.

Would having a date in the future in that data cause a problem. As this year it is set to 31 / 12 / 2025 ?

vingerha · January 27, 2025, 9:21am

Not that I know off, and you can modify the hover-over too but in fact you are further fixing a symptom and not the cause, which I believe is not really the way to go. What I find interesting is that your hover shows once 12:00 and now 23:59 so I continue to guess that the code to extract is not having the same dat-response as in your list above. Apex usually shows the data as it comes

davo · January 27, 2025, 9:32am

The group_by: func: raw does show the correct date. Just misaligns it with labels.

Is there some way I can read the data into a text box or similar to see exactly what data is coming in.

I believe this is a similar problem:

https://github.com/apexcharts/apexcharts.js/issues/1688

vingerha · January 27, 2025, 9:51am

Not that I know of and the only way that I have done in the past is break it down and send data as y-values. e.g. instead of val[1] you can try to send val[0] broken down to date-value or hour-value, e.g. showing only 31 or 1, or to hour value showing only 23 or 12 (or whatever you get back)
I hope it makes sense

davo · January 27, 2025, 10:09am

Thanks for your help @vingerha.

I got what I want with the columns in right place and data correct, but date is out by 180 days by offsetting +180d. Not ideal, but better than before.

I think it is a problem with apexcharts ofter reading bug reports on github.

Do you know if its possible to only show year in hover box, not day and h:m:s ?

vingerha · January 27, 2025, 10:17am

It can be done and the only piece of reference that I have is in this code. Likely way more code than you need but you will probably get it done …
fix for hover values

vingerha · January 27, 2025, 10:31am

It can be done simpler…example:

apex_config:
  tooltip:
    enabled: true
    shared: true
    x:
      format: ddd dd MMM

davo · January 27, 2025, 11:23am

This is good. Where did you find this information ?

It looks like this now:

Do you know how romove Yearly Usage Kw(+180d) and replace with kW value (eg kW 7,827.9) in tooltip box, and to get rid of the tooltip box with year at bottom of column ?

Or is there a way to just put kW for year at top of each column so don’t need tooltip - maybe vertically ?

vingerha · January 27, 2025, 12:10pm

Searching ‘hover’ or ‘tooltip’

Peethiplatsch · January 28, 2025, 10:07pm

Hello tgether, I have a question regarding my chart I want to create. I played around a lot and I feel like I almost got it.
But I can’t get the tooltip working with both entities. I only get the one I am hovering over.
I also saw that it can’t be grouped if the x axis entries do not match. Unfortunately they match
This is the current tooltip.

Here is the tooltip code under apex_config.
tooltip:
enabled: true
shared: true
intersect: false
followCursor: false
x:
show: true
format: dd.MMMM \a\t HH \U\h\r

The data comes from my energy price integration which is custom. It has the following format. Only change between my two entities is that the price differs:
raw_today:

hour: ‘2025-01-28T00:00:00+01:00’
price: 0.7
hour: ‘2025-01-28T01:00:00+01:00’
price: 0.692

If you need additional code, I am happy to share it.

kkoenen · January 30, 2025, 12:43pm

On my implementation, since today (30th of Jan ´25), Apex charts are not working anymore. Just shows “Loading…” for all 3 charts (power usage, internet speed up & down). When I edit dashboard, and save, it does show graphs, but the circle upper right keeps spinning (loading mode). When I configure some other card to show the same data, that works fine. This leads me to believe it is in fact an ApexCharts problem… Anyone else experiencing this? Or local issue?

tcarlsen · January 30, 2025, 3:56pm

how did you get your bar’s to sit right on the hour marker? mine has there right side on the marker and it does not look good

Kugelblitz · January 30, 2025, 8:52pm

That looks really cool and amazing that it works… but also like a lot of work…
its ok if you do it for one sensor but if its a bunch of them…
I bookmark it and come back later

Peethiplatsch · January 31, 2025, 9:27pm

I moved the bars +30min. Here is the code for my data generator.
But it brings the problem, that you need to adjust the format of the tooltip. Because this will now show always e.g. 18:30. This would be within the apex_config.

  tooltip:
    enabled: true
    shared: true
    intersect: false
    followCursor: false
    x:
      show: true
      format: dd.MMMM \a\t HH \U\h\r

data_generator: |
      var today = entity.attributes.raw_today.map((start, index) => {
        return [new Date(start["hour"]).getTime() + 1800000, entity.attributes.raw_today[index]["price"]];
      });
      if (entity.attributes.tomorrow_valid === false) {
        var tomorrow = entity.attributes.forecast.map((hour, index) => {
          return [new Date(hour["hour"]).getTime() + 1800000, entity.attributes.forecast[index]["price"]];        
        });
      } 
      else {
        var tomorrow = entity.attributes.raw_tomorrow.map((hour, index) => {
          return[new Date(hour["hour"]).getTime() + 1800000, entity.attributes.raw_tomorrow[index]["price"]];
        });
      }
      return today.concat(tomorrow)

Multijeep24 · February 1, 2025, 1:29am

Why I’m I getting these values? I should be getting dates since these are sensors configured in configuration.yaml

Here’s my configuration.yaml :

template:
  - sensor:
      - name: "Tomato Sowing Date"
        unique_id: tomato_sowing_date
        state: "2025-02-15T00:00:00Z"  # Example date (YYYY-MM-DD)
        attributes:
          start: "2025-02-15T00:00:00Z"  # Ensure ISO format
          end: "2025-04-01T00:00:00Z"

      - name: "Lettuce Harvest Date"
        unique_id: lettuce_harvest_date
        state: "2025-05-10T00:00:00Z"  # Example date (YYYY-MM-DD)
        attributes:
          start: "2025-05-10T00:00:00Z"
          end: "2025-06-15T00:00:00Z"

Resulting Chart :

Apex Chart YAML :

type: custom:apexcharts-card
graph_span: 6M
span:
  start: month
  offset: "-1M"
header:
  title: Sowing Calendar
  show: true
series:
  - entity: sensor.tomato_sowing_date
    name: Tomato Sowing
    data_generator: >
      return [{ x: "Tomato Sowing", y: [new
      Date(entity.attributes.start).getTime(), new
      Date(entity.attributes.end).getTime()] }];
  - entity: sensor.lettuce_harvest_date
    name: Lettuce Harvest
    data_generator: >
      return [{ x: "Lettuce Harvest", y: [new
      Date(entity.attributes.start).getTime(), new
      Date(entity.attributes.end).getTime()] }];
chart_type: scatter
apex_config:
  chart:
    type: rangeBar
  plotOptions:
    bar:
      horizontal: true
  xaxis:
    type: datetime
  yaxis:
    categories:
      - Tomato Sowing
      - Lettuce Harvest

davo · February 1, 2025, 4:39am

Hi, How can I change a number to a negative.
I have a utility meter that shows solar exported to the grid, However it is a positive number and I would like to show it as a negative in apexcharts.

I have tried transform as in the below but it does not work.

series:
  - entity: sensor.energy_grid_export_hourly
    type: column
    name: Export
    transform: return -x;
    float_precision: 2
    group_by:
      func: delta
      duration: 1h

I can multiply or divide x in transform and it works, but not subtract or add.