[for EXPERTS] How to show the average daily curve of last 7 days

[skydado]

I want to show the current day solar production trend (xaxis=hours of the day) and compare it with the average production of the last 7 days.

In other words, if at 11am the solar production in the last 7 days has had an average of 1Kw AT THAT SPECIFIC TIME, I want to show a curve that at 11 shows 1Kw, and so on. Hope it is clear enough.

I managed to compare the current day curve with a previous day curve with offset, but wondering if I can show the average of a longer period.

[perlchamp]

hi,
try graph_span and span
Maybe reading the doku would help either ?

Good luck
And then please mark it as solved.

[skydado]

if it were that simple, I probably wouldn't have asked here, right? And I don't think anyone pointed me to the solution... so no, it's not solved.

[perlchamp]

ah, ok.
Then i need more informations. Readable code, images, etc.
But first try this:
to display a graph, you need an entitiy, right? use graph_span and span.
to display a second graph, you need a second entity-section. It can be the same entitiy.
In both entity-sections use the attribut 'show' with parameter in_header (if you want to display there) and in_chart (if you want to display there [too]).
You also have the possibility to p.ex. display max and min values only in the header. Therefor another entity (the same name), set time-options (duration), and use the group_by and the func max and min. Something like that:

Good luck !

[skydado]

This is the current situation.

The green/yellow line shows the today's solar production.
The grey area graph shows the yesterday's solar production.
Instead of just yesterday's prod, I'd like to have this grey area graph showing the average production in the last 7 days, in the same way now it shows yesterday's one. In other words, same graph but it is the average of the last 7 days.

This is the current code:

type: custom:apexcharts-card
apex_config:
  legend:
    show: false
  chart:
    height: 180px
  grid:
    show: false
  yaxis:
    show: false
update_interval: 1min
experimental:
  color_threshold: true
graph_span: 12h
span:
  start: day
  offset: +8h
header:
  show: true
  title: Produzione SOLARE
  show_states: true
now:
  show: true
  color: red
  label: Now
series:
  - entity: sensor.shellyrosso_pv_15_power
    group_by:
      func: avg
      duration: 5min
    stroke_width: 3
    curve: smooth
    extend_to: false
    float_precision: 0
    show:
      extremas: max+time
      in_header: false
    color_threshold:
      - value: 0
        color: gold
      - value: 500
        color: lawngreen
  - entity: sensor.shellyrosso_pv_15_power
    offset: "-1d"
    group_by:
      func: avg
      duration: 5min
    stroke_width: 0
    color: lightgrey
    type: area
    curve: smooth
    show:
      extremas: max+time
      in_header: false

[perlchamp]

ok.
My knowledge:
i think you cannot mix different graph_span and span values. both over 7days or whatever. But BOTH !
You cannot set "1d" for the first graph/curve and "7d" for the second graph/curve.
Maybe you can choose "7d" and via script or template which data-buckets should (not) be displayed.
I don't know. Sorry.
I think either, the title of your question should be more differentiated. Nobody will answer, if the meaning is: "don't ask, read the docu instead"

Good luck, and please share your solution.

[skydado]

Ok, let's simplify the goal and just show the 7 days historical average data. Even in this case, graph_span is not helping, at least as far as I understand. Any suggestion?
About the title too, any suggestion?

[perlchamp]

graph_span: 7d
span:
  end: day
  offset: "-0"

This will display the graph about the LAST (-> end !) 7days. The last of the seven day is here TODAY (end day). Take 'end: minute' and then the last value is one minute ago and the first value 72460 minutes ago (7days).
You have to use 'end' to display history-data

For average data:

- entity: sensor.the_same
    name: AVG
    group_by:
      func: avg
      duration: 7d
    color: tomato
    show:
      hidden_by_default: false
      legend_value: false/true
      in_header: true # show avg-value in header
      in_chart: false # true displays avg-graph in chart
    float_precision: 2 # number of decimals (xxx.yy)

Your title:
You have ONE timeline, if you display a graph (p.ex. width 10cm) which contains data over 7days, then you cannot display a graph (width 10cm, too) over only one day, because of the same data. They have a timestamp and therefor is x-Axis. ONE x-Axis...
I really don't know what you want. Display a graph over 7 days and the othe over 1 day streched ? My knowledge: unpossible.

AND please read the docu. The docu is really well and all my knowledge i have from there, so...

[skydado]

Ok, it's clear my request is not clear enough.
Let's suppose to have a sensor that on each hour of the day registers the following values:

<style> </style>

hours		day1	day2	day3	day4	day5	day6	day7		average
8		9.3	3.7	8.1	8.9	9.1	0.9	2.5		6.1
9		6.5	1.8	3.2	6.2	3.8	1.0	2.7		3.6
10		5.1	1.4	6.1	8.2	8.3	3.5	6.1		5.5
11		0.7	4.1	5.3	4.1	3.6	0.6	4.7		3.3
12		3.5	3.7	4.6	7.1	9.0	4.9	2.7		5.1
13		5.9	6.2	7.9	6.9	6.4	8.9	9.4		7.4
14		0.7	3.8	0.6	8.9	4.7	1.4	4.4		3.5
15		9.9	8.5	0.0	2.7	6.6	9.4	6.5		6.2
16		3.2	2.9	3.8	1.1	0.7	1.0	7.7		2.9
17		4.9	0.2	6.1	3.5	0.8	7.2	8.6		4.5
18		7.5	5.9	6.0	8.6	1.5	0.1	1.7		4.5
19		5.6	5.9	8.6	1.6	0.7	5.7	5.5		4.8

I want my graph to show the hourly average (last column).
So I will have a graph similar to the following:

Hope this is more clear.
And yes, I read the docu, but I didn't find any solution for this.

[perlchamp]

ups, ok... hmm
the last row should covered with:

- entity: sensor.bla_blub        # der entsprechende Sensor
    name: AVG                    # frei wählbar, z.B. Durchschnitt
    group_by:                    # gesammelte Daten nach dem Durchschnitt filtern
      func: avg                  # Funktion Durchschnitt ermitteln
      duration: 1h               # Durchschnitt der letzten Stunde

show: 
  in_chart: true                 # Daten werden als Graf angezeigt

You can rename the units and/or set the gap between the units of the xaxis.
You can set the update_intervall to 1h, so you will get data once per hour.
Set the graph_span and span over day
Play with the duration-value...
The last way, maybe is to change the raw-data via data_generator. But this is new territory for me.
I'm still a beginner, so...

If i find the time, i will try to make a avg-graph for my goldchart the way you're looking for, but...
Maybe the type column diagramm would be the right way...

Your title could be: How to display the 7 day-avg-value (Y-Axis) per hour of the last 24 hours (X-Axis) ?

You're german ?

Good luck !

[perlchamp]

i've found a (dirty and a can-life-with-that) solution :

type: custom:apexcharts-card
header:
  show: true
  title: blablub
  show_states: true
  colorize_states: true
stacked: false

New:
stacked: false

graph-settings:

graph_span: 14h
span:
  start: day
  offset: "+7h"  

first graph - today:

series:
  - entity: sensor.goldpreis_yaml
    name: Durchschnitt
    type: column
    color: green
    float_precision: 2
    group_by:
      func: avg
      duration: 1h
    show:
      in_header: false
      legend_value: true
      in_legend: false

second graph - yesterday (<- offset)

  - entity: sensor.goldpreis_yaml
    name: Durchschnitt
    type: column
    color: red
    float_precision: 2

    offset: "-1day"

    group_by:
      func: avg
      duration: 1h
    show:
      in_header: false
      legend_value: true
      in_legend: false

You need x Graphs, so the same x entities, with the needed offset (-1day, -2day, -3day...). And give each a different Color. I took green, red, blue, yellow, orange, pink and Magenta.
AND:
Don't change the names of the entities (Durchschnitt). They have to be the same, each ! You can all name 'blablub', but ALL.

Now please copy first my xaxis config. You can change it later, so you can follow..., new: tickAmount, stepSize, format. :

apex_config:
  xaxis:
    tickAmount: 14 # one tick per displayed xaxis-hour
    stepSize: 1h
    position: bottom
    labels:
      format: "HH:mm" 
      show: true
      offsetX: 0
      style:
        colors: "#777777"
        fontSize: 14px
        fontFamily: Bahnschrift
    axisBorder:
      show: true
      color: "#555555"
      height: 0.5
    axisTicks:
      show: true
      color: "#555555"
    title:
      text: ""
      style:
        color: "#777777"
        fontFamily: Bahnschrift
    crosshairs:
      show: false
    tooltip:
      enabled: false

and please copy my card-style, because then you see, what i see. You can change it later, too. The width garantee that all (14) hours will be displayed on the xaxis.
And no tunty border-radius:

card_mod:
  style: |
    ha-card {
      border-radius: 0px;
      margin:  -5px 0px 0px -5px;
      padding: 0px 0px 20px 0px;
      #color: #1C1C1C;
      font-size: 1em;
      width: 700px;
    }

I KNOW, it's not (yet), what you want, but please check the data if they are right.
You should now see 7 avg-columns from today until 7days ago, side by side, per hour.
We are on the right way, aren't we ? If you can life with 'stepped' columns, then give all the same color. The today graph then should be a line, so you can compare the data.

Set now the Attribute 'stacked' to true.
=> now you see only one column per hour

PROBLEM: it is the sum of all 7.
SOLUTION: to each entity add the following line directly UNDER - NOT in - the '-entity:'-attribute:
transform: return x / 7;

EXPLANATION: You have 7 days, so each day-avg-value has to become divided by 7, so that the sum of the averages results nearly the real one.

DEPENDED PROBLEM:
The Tool-Tipps now show the divided value for each part of the column, and not the sum of the column.
If that doesn't matter, you can change the color of each entity to the same, so the column only has now one color.
But now you can't range the y-axis, and the values in the tool-tipps are wrong.

This should nearly be, what you want. With the column Diagramm you have to life, but a 8th graph for today as line or area… so you can compare data per se.

To display a smaller range of y-values you can set min and max.

NO THAT'S WRONG ! You can't change a loot of things (design, axis). It's only for watching and visual displaying the data...
I don't like this dirty solution. With the stepped columns i could life...

Good luck !

[skydado]

thanks a lot for your effort! Pls allow me some days as I'm really busy these weeks. I'll try asap and let you know ...

…

Il 01/02/2025 08:55 CET perlchamp ***@***.***> ha scritto: i've found a (dirty and a can-life-with-that) solution : type: custom:apexcharts-card header: show: true title: blablub show_states: true colorize_states: true stacked: false New: stacked: false graph-settings: graph_span: 14h span: start: day offset: "+7h" first graph - today: series: - entity: sensor.goldpreis_yaml name: Durchschnitt type: column color: green float_precision: 2 group_by: func: avg duration: 1h show: in_header: false legend_value: true in_legend: false second graph - yesterday (<- offset) - entity: sensor.goldpreis_yaml name: Durchschnitt type: column color: red float_precision: 2 offset: "-1day" group_by: func: avg duration: 1h show: in_header: false legend_value: true in_legend: false You need x Graphs, so the same x entities, with the needed offset (-1day, -2day, -3day...). And give each a different Color. I took green, red, blue, yellow, orange, pink and Magenta. AND: Don't change the names of the entities (Durchschnitt). They have to be the same, each ! You can all name 'blablub', but ALL. Now please copy first my xaxis config. You can change it later, so you can follow..., new: tickAmount, stepSize, format. : apex_config: xaxis: tickAmount: 14 # one tick per displayed xaxis-hour stepSize: 1h position: bottom labels: format: "HH:mm" show: true offsetX: 0 style: colors: "#777777" fontSize: 14px fontFamily: Bahnschrift axisBorder: show: true color: "#555555" height: 0.5 axisTicks: show: true color: "#555555" title: text: "" style: color: "#777777" fontFamily: Bahnschrift crosshairs: show: false tooltip: enabled: false and please copy my card-style, because then you see, what i see. You can change it later, too. The width garantee that all (14) hours will be displayed on the xaxis. And no tunty border-radius: card_mod: style: | ha-card { border-radius: 0px; margin: -5px 0px 0px -5px; padding: 0px 0px 20px 0px; #color: #1C1C1C; font-size: 1em; width: 700px; } I KNOW, it's not (yet), what you want, but please check the data if they are right. You should now see 7 avg-columns from today until 7days ago, side by side, per hour. We are on the right way, aren't we ? If you can life with 'stepped' columns, then give all the same color. The today graph then should be a line, so you can compare the data. Set now the Attribute 'stacked' to true. => now you see only one column per hour PROBLEM: it is the sum of all 7. SOLUTION: to each entity add the following line directly UNDER - NOT in - the '-entity:'-attribute: transform: return x / 7; EXPLANATION: You have 7 days, so each day-avg-value has to become divided by 7, so that the sum of the averages results nearly the real one. DEPENDED PROBLEM: The Tool-Tipps now show the divided value for each part of the column, and not the sum of the column. If that doesn't matter, you can change the color of each entity to the same, so the column only has now one color. But now you can't range the y-axis, and the values in the tool-tipps are wrong. This should nearly be, what you want. With the column Diagramm you have to life, but a 8th graph for today as line or area… so you can compare data per se. > > To display a smaller range of y-values you can set min and max. > NO THAT'S WRONG ! You can't change a loot of things (design, axis). It's only for watching and visual displaying the data... I don't like this dirty solution. With the stepped columns i could life... Good luck ! — Reply to this email directly, view it on GitHub #716 (reply in thread), or unsubscribe https://github.com/notifications/unsubscribe-auth/BCNMO2P275EZV4FCDXHDMT32NR4V7AVCNFSM6AAAAABV4TA2W6VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTEMBSGQ2DONI. You are receiving this because you authored the thread.Message ID: ***@***.***>

[ptryk]

I have also been after this type of feature, see here:
https://community.home-assistant.io/t/hourly-statistics-for-values-over-a-set-span-of-time-days/859600

I ended up now using an AI helped sql script to make this type of calculation - maybe it can be of help you as well? - unfortunately you have to wait 24hrs for it to run through a day of data, but then it is running .. the alternative of waiting a week, month, quarter, year to get the average is much worse I think . .. I will follow to see if apex can manage this as doing it on the fly would be great!!

[perlchamp]

thank you. I will 'throw' an eye on it.

I ended up now using an AI helped sql script to make this type of calculation - [...]

Nice, something new to learn. The day is rescued ;-)

I will follow to see if apex can manage this as doing it on the fly would be great!!

yes, please do so...