Monthly Grouped Bar Chart

jceddy · March 12, 2020, 10:01pm

I have some time series data for three “sub-categories” that I would like to display grouped by month, similar to what I have shown below:

chart003

The data is coming from an MS SQL data source, so the way I return the data is flexible.

Any ideas on how to display data grouped by month this way in a bar graph?

jceddy · March 13, 2020, 2:13pm

Just some more info, here is example data from 2019:

time       metric Profit
1546322400 Sub1   39.492981
1546322400 Sub2   35.120937
1546322400 Sub3   30.115721
1549000800 Sub1   27.780488
1549000800 Sub2   31.356590
1549000800 Sub3   32.812336
1551420000 Sub1   29.871925
1551420000 Sub2   31.004485
1551420000 Sub3   29.528173
1554098400 Sub2   26.957813
1554098400 Sub3   37.404961
1554098400 Sub1   39.412307
1556690400 Sub2   27.782302
1556690400 Sub3   30.609764
1556690400 Sub1   31.070458
1559368800 Sub1   22.051640
1559368800 Sub3   37.339840
1559368800 Sub2   36.985437
1561960800 Sub2   34.927834
1561960800 Sub3   33.174676
1561960800 Sub1   30.144342
1564639200 Sub3   27.845438
1564639200 Sub1   28.747395
1564639200 Sub2   39.851462
1567317600 Sub1   33.260239
1567317600 Sub3   30.683247
1567317600 Sub2   28.364973
1569909600 Sub2   20.971850
1569909600 Sub3   18.716690
1569909600 Sub1   17.734560
1572588000 Sub1   32.923395
1572588000 Sub3   33.026336
1572588000 Sub2   28.848296
1575180000 Sub3   40.194259
1575180000 Sub1   29.120295
1575180000 Sub2   30.119459

I had to add the month to the metric to get any kind of grouping in the output chart:

time       metric      Profit
1546322400 Jan-19 Sub1 39.492981
1546322400 Jan-19 Sub2 35.120937
1546322400 Jan-19 Sub3 30.115721
1549000800 Feb-19 Sub1 27.780488
1549000800 Feb-19 Sub2 31.356590
1549000800 Feb-19 Sub3 32.812336
1551420000 Mar-19 Sub1 29.871925
1551420000 Mar-19 Sub2 31.004485
1551420000 Mar-19 Sub3 29.528173
1554098400 Apr-19 Sub2 26.957813
1554098400 Apr-19 Sub3 37.404961
1554098400 Apr-19 Sub1 39.412307
1556690400 May-19 Sub2 27.782302
1556690400 May-19 Sub3 30.609764
1556690400 May-19 Sub1 31.070458
1559368800 Jun-19 Sub1 22.051640
1559368800 Jun-19 Sub3 37.339840
1559368800 Jun-19 Sub2 36.985437
1561960800 Jul-19 Sub2 34.927834
1561960800 Jul-19 Sub3 33.174676
1561960800 Jul-19 Sub1 30.144342
1564639200 Aug-19 Sub3 27.845438
1564639200 Aug-19 Sub1 28.747395
1564639200 Aug-19 Sub2 39.851462
1567317600 Sep-19 Sub1 33.260239
1567317600 Sep-19 Sub3 30.683247
1567317600 Sep-19 Sub2 28.364973
1569909600 Oct-19 Sub2 20.971850
1569909600 Oct-19 Sub3 18.716690
1569909600 Oct-19 Sub1 17.734560
1572588000 Nov-19 Sub1 32.923395
1572588000 Nov-19 Sub3 33.026336
1572588000 Nov-19 Sub2 28.848296
1575180000 Dec-19 Sub3 40.194259
1575180000 Dec-19 Sub1 29.120295
1575180000 Dec-19 Sub2 30.119459

And this is the closest I’ve been able to get to the desired chart:

jceddy · March 13, 2020, 2:37pm

Well, I found the pertinent Github issue: https://github.com/grafana/grafana/issues/870

This is the second such roadblock linked to a known issue I’ve run into with grafana inside 3 days of working with it. Guess I might need to switch to something that is better supported.

jceddy · March 13, 2020, 2:46pm

Looks like this plugin might do what I want:

castillo92 · March 17, 2020, 9:27pm

Did you archive the solution with the plugin?

If yes, could you show how it’s look please? I need to do something similar

jceddy · March 18, 2020, 2:32am

I actually fixed some issues and added an option to the groupedbarchart plugin, and it works really well for me. I will try to get some screen shots tomorrow.

yantis24 · March 18, 2020, 12:03pm

Hi @jceddy,

I am interested to know how you did the query and how you fixed the charts issue.
I have been working on the same type of work for 3 days now, I am very new with Grafana and mySql.
I need to display the monthly grouped charts of active users based on startDate and endDate.
so far I only managed to display the total new starters in monthly group not the total active users.

I hope you don’t mind me asking these questions.

thank you,

yanti

jceddy · March 18, 2020, 1:04pm

Here’s an example of a chart using the groupedchartplugin:

In this case, you need a table query that returns three columns:

The first column contains the grouping values (in this case, “Jan-19”, “Feb-19”, “Mar-19”, etc.), the second column contains the name of the metric being grouped, which determine the separate colored bars in the groups (in this case, it is three company names), and the third value is the actual value that determines the height of the bar in the chart.

There is an option to stack these (actually the default), rather than show them side-by-side, as I do here, also when displayed side-by-side, the value can be shown at the top of each bar (that is actually also the default behavior, but I have it disables on this graph).

Hope this helps.

Cheers,
Joe

llewellyn · April 12, 2020, 3:49pm

I need some help.
My Grafana-version is v6.7.1.
I’ve installed the plugin groupedbarchart.
I have the following table

|time|value|metrics|
|01.04.2020 23:58:00|0,496|Waschmaschine|
|01.04.2020 23:58:00|0,535|Gefrier|
|02.04.2020 23:58:00|0,009|Waschmaschine|
|02.04.2020 23:58:00|0,343|Gefrier|
|03.04.2020 23:58:00|0,009|Waschmaschine|
|03.04.2020 23:58:00|0,519|Gefrier|
|04.04.2020 23:58:00|1,199|Waschmaschine|
|04.04.2020 23:58:00|0,341|Gefrier|
|05.04.2020 23:58:00|0,458|Waschmaschine|
|05.04.2020 23:58:00|0,433|Gefrier|
|06.04.2020 23:58:00|0,287|Waschmaschine|
|06.04.2020 23:58:00|0,365|Gefrier|
|07.04.2020 23:58:00|0,789|Waschmaschine|
|07.04.2020 23:58:00|0,556|Gefrier|
|08.04.2020 23:58:00|0,01|Waschmaschine|
|08.04.2020 23:58:00|0,35|Gefrier|
|09.04.2020 23:58:00|1,031|Waschmaschine|
|09.04.2020 23:58:00|0,542|Gefrier|
|10.04.2020 23:58:00|0,009|Waschmaschine|
|10.04.2020 23:58:00|0,541|Gefrier|

The Query Inspector shows me that the data is correct retrieved.

But the panels show no data.

My Select-Trys are:

SELECT UNIX_TIMESTAMP(time) as time_sec, convert(100*value, double) as value, metrics as metric FROM test WHERE $__timeFilter(time) ORDER BY time ASC
SELECT UNIX_TIMESTAMP(time) as time_sec, convert(100*value, double) as value, metrics as metric FROM test
SELECT UNIX_TIMESTAMP(time) as time_sec, value as value, metrics as metric FROM test

and many other variants.
But the result is an empty panel

Every hint is welcome

Many thanks and greetings

Llewellyn

padmanabhan04064 · May 8, 2020, 4:37am

Hi @llewellyn,

I am also working on the same scenario, Did you got the solution, if you have any hint could you please post it here.

Thanks,
Paddy.

llewellyn · May 9, 2020, 8:52am

Hi Paddy,

sorry, no answers until yet.

padmanabhan04064 · May 9, 2020, 8:43pm

Hi @jceddy
I am using mysql DB. So, in my query I added now() as time as the first column.

Did you change anything in source code?

Please provide some more details to get this result.

Thanks,
Paddy.

aernst · May 22, 2020, 8:48pm

Hi,
I’m struggling with the monthly grouped bar chart as well and it is a nightmare.
Obviously you have managed it.
Could you share with us the query used to create this chart?
(Although I use MSSQL I guess it is similar to MYSQL)

Regards

saikatsadhukhan · July 1, 2020, 5:23am

Anyone tried this with Flux data source. I need similar types of graph but I need for Flux data source.

rajeswari · October 23, 2020, 4:59am

Please change the query type from timeseries to table

andrepoandrade · December 11, 2020, 10:41pm

This groupedchart plugin worked nicely for me!
I’m using Grafana 7.3.4 and sql server and my query returns 3 columns
Collumn1 = Month name as varchar for grouping results
collumn2 = metrics as varchar
collum3 = count as integer

For the query results select Format As Table option, after that you should select Grouped Bar Chart visualization and chart type as bar chart.

edwin2 · March 7, 2021, 11:51am

Hello,

I’m trying to create a barchart with 2 value’s stacked and a third next to it.
I found this plugin and looks to be an option.

To start, I started with one query, but got the same result as Llewellyn with only a small white line.

I guess I need to do something else with the query, but not clear what.
I’v got the one like below.
This one works on the Bar chart from Grafana (only difference is Format As is there Time Series).

query

Does anyone have an example of the correct settings for the query that is needed?

Thanks,
Edwin

qambarsabbas · May 11, 2021, 7:21pm

Hey Everyone,
I am new to Grafana, and I need to create a grouped bar chart as well

I understand that I needed to download the plugin from the GitHub page. I downloaded the zip file and unzipped it in my local grafana plug in folder.
Program Files\GrafanaLabs\grafana\public\app\plugins\panel\grafana-groupedbarchart-panel-master

I restarted the grafana service in my task manager, then restarted the local grafana web page.
Then I opened the plugin from the configuration tab.

I saw that the plugin was there but when I opened it I saw this

can someone please help me set it up.

Thank you

giotshibangu · June 13, 2022, 4:33pm

I have done and I still getting an extra row

Topic		Replies	Views
Group bars by metric Time Series Panel	1	418	October 5, 2019
Need help grouping and stacking bar chart Bar Chart Panel	0	213	April 10, 2023
Displaying grouped data in bargraph MySQL mysql	0	443	January 20, 2020
Struggeling with bar chart for monthly consumption Bar Chart Panel	5	468	January 15, 2023
How to draw a cluster bar chart? Time Series Panel mysql	0	1037	August 29, 2018

Monthly Grouped Bar Chart

Related topics