Graphs for Month

[angeroluca]

Hi to all.
I’ve a table in this format:
datetime | energy
I’d like to create a graph that represents the energy by months, so in the x-axis
January, February, March, April, etc.
And for each month a serie for each year, showing the sum of energy for that month and that year, to compare the energy each month in all years.

I’m thinking it’s not possibile in grafana…
Thanks a lot.

[Nefariis]

A few simple questions - What database are you using? and is Grafana currently connected to the database with a working connection?

Grafana can group time series by any range of time using the Group By feature (depending on data source)

[angeroluca]

I’m using Mysql.
The group by feature works for tables, but not for graphs.
If I change db, changes the behavior of grafana? (sorry I’m learning grafana since few weeks…)
thanks.

[alexsmart]

Im using InfluxDB amd have the same problem. I have an energy counter data and want to get a monthly statistics, i.e. January power consumption = 01 Feb meter readings - 01 Jan meter readings, and so on. Still dont get how to do it

[Nefariis]

@angeroluca - Group By should work for graphs as well - can you put a csv of your table somewhere online? Ill see if I can create the graph sometime today.

@alexsmart - Can you go toggle the “Text Edit Mode” for the query and post it your query here?

[castillo92]

@angeroluca

o create a graph that represents the energy by months, so in the x-axis
January, February, March, April, etc.

The first you have to know is how you store your metrics and how to explore it.

I log a few power metrics once per second. Then I create some mean metrics like mean minute and hourly means

To show month and year metrics I use the hourly mean of each day.

Are you looking for something like this?

This month:

Previous month

This year

Previous year

[angeroluca]

Thanks, you can download the CSV (with the result I would) here: WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free
The columns are:
IDImd: ID record
IDImp: ID for the plant
DataOra: date/time of the record
Anno: year of datetime
Mese: month of datetime
Prodotta1: energy of that period

In the excel file I put the graph like I wold see in grafana, if possibile.
Thanks for your support.

[angeroluca]

Something like that.
Below the example with my data, grouped by month

But I need to group all months of many years in several bars, like that:

Thankyou

[Nefariis]

uhhh… I believe you just need to use order by if you are using SQL

SELECT     Prodotta1
FROM       YourTable
order by     Mese, Anno

[angeroluca]

Sorry but in this way I see only the table, without grouping…

[Nefariis]

Alright with a little tinkering this morning I was able to accomplish what you wanted -

First you need to download the multistat plugin located here

Multistat plugin for Grafana | Grafana Labs

Next you need to add a column in your data that combines the month and the year, something like “MM-YYYY”

Then you can use the query -
select sum("usage") from test group by newColumn

then you set the label column to your newColumn and the value column to sum - and viola, you should have a graph that looks similar to this.

[angeroluca]

Great!!!
I used that plugin, a little different from your istructions but the result is the same!

Thank you a lot!!

[coolon04]

What type of Visualisation did you use here (its not bar plot right?)

[sonybuoay]

Hello. i am using influxdb and grafana under home assistant. i tried to create the format you propose but it is not workig for me. i have a daily total consumption that i would like to graph as yearly consumption. When i use group by 4w it will take the max value recorded in the group of 4w. if i group by 1d, the total value is correctly calculated, but the graph splits the bar in a daily view. how can i configure grafana to show me the grouped by 4w but to show me the toal value of the energy cosumed in the 4w?