Stacked Bar Chart for 3 years

[gedomo1]

Hello!

I would like to display the consumption of my house for each year.
I like to use a stacked bar chart… this is working so far.

But when I try to set another stacked barchart beside, he stack all values to one chart.
I like to display the consumption and delivery for each month and beside should be the same barchart for last year.

Hope I was to explain.
For each month stacked the consumption and delivery (as in the first chart)
And beside the same stacked for last year and one for the yearbefore last year.

So the month has 3 stacked bars

Thank you

[grant2]

Welcome @gedomo1

What is your datasource, and can you share the queries that you have created for the graphs shown?

[gedomo1]

Hello @grant2

The Datasource is Influxdb mit flux query.
I read 3 times the same, with differents start/end dates.

Here to display the power consumption/delivery each month

query 1:

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Vienna")
startDate = date.truncate(t: today(), unit: 1y)

from(bucket: "Home")
  |> range(start: startDate, stop: today())
  |> filter(fn: (r) => r["_measurement"] == "PV")
  |> filter(fn: (r) => r["Powermeter"] == "Hauptzaehler")
  |> filter(fn: (r) => r["_field"] == "Total Geliefert" or r["_field"] == "Totaler Bezug")
  |> difference()
  |> filter(fn: (r) => r["_value"] > 0 and r["_value"] < 400)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> timeShift(duration: -1s, columns: ["_time"])
  |> yield()

query 2:

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Vienna")
startDate = date.truncate(t: today(), unit: 1y)

from(bucket: "Home")
  |> range(start: date.sub(from: startDate, d: 2y), stop: date.sub(from: startDate, d: 1y))
  |> filter(fn: (r) => r["_measurement"] == "PV")
  |> filter(fn: (r) => r["Powermeter"] == "Hauptzaehler")
  |> filter(fn: (r) => r["_field"] == "Total Geliefert" or r["_field"] == "Totaler Bezug")
  |> difference()
  |> filter(fn: (r) => r["_value"] > 0 and r["_value"] < 400)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> timeShift(duration: -1s, columns: ["_time"])
  |> yield(name:"this year")

query 3:

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Vienna")
startDate = date.truncate(t: today(), unit: 1y)

from(bucket: "Home")
  |> range(start: date.sub(from: startDate, d: 3y), stop: date.sub(from: startDate, d: 2y))
  |> filter(fn: (r) => r["_measurement"] == "PV")
  |> filter(fn: (r) => r["Powermeter"] == "Hauptzaehler")
  |> filter(fn: (r) => r["_field"] == "Total Geliefert" or r["_field"] == "Totaler Bezug")
  |> difference()
  |> filter(fn: (r) => r["_value"] > 0 and r["_value"] < 400)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> timeShift(duration: -1s, columns: ["_time"])
  |> yield(name:"this year")

[grant2]

If you put the query below in Influx Data Explorer and toggle the view to View Raw Data, can you share a screenshot of what it looks like?

@gedomo1

import "timezone"
import "date"
option location = timezone.location(name: "Europe/Vienna")
startDate = date.truncate(t: today(), unit: 1y)

from(bucket: "Home")
  |> range(start: startDate, stop: today())
  |> filter(fn: (r) => r["_measurement"] == "PV")
  |> filter(fn: (r) => r["Powermeter"] == "Hauptzaehler")
  |> filter(fn: (r) => r["_field"] == "Total Geliefert" or r["_field"] == "Totaler Bezug")
  |> difference()
  |> filter(fn: (r) => r["_value"] > 0 and r["_value"] < 400)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> timeShift(duration: -1s, columns: ["_time"])
  |> yield()

[gedomo1]

[grant2]

Can you change the last 3 lines to this and then see how it looks in Grafana?

  |> timeShift(duration: -1s, columns: ["_time"])
  |> group()
  |> yield()

[gedomo1]

Thank you!

Than I get this:

But I´m rethinking … I like to display the bar vor this year, last and before last … so I need to modify the year time … otherwise he display not at the same month.

Here an example from a web:

It display for each month the energy consumption … I would like to have this chart, but stacked for delivery/consumption.

Is this possible?

EDIT:
May it is better to made one bar chart for consumption and one for delivery … then I “just” need to modify the year to get it

EDIT 2:
Is it possible to made a pivot based on months?

[grant2]

Hi @gedomo1

Unfortunately I do not have much time, but your question looks to be similar to this post/solution.

Can you have a look at that and see if anything there helps?

Also maybe this.

[grant2]

Also, for stacking two or more time series “bars”, these settings worked for me:

[pretwick]

You want something like this ?

You can’t do it directly in Grafana.

The data from my smart meter is send to a MySql database. For this graph I use intermediate tables with the year totals like this.

Those tables are build automatically by a script from the recorded data once per day (during the night).

Maybe you can do that in Influx too.

I hope this helps.

Peter