Monthly Grouped Bar Chart

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?

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:

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.

Looks like this plugin might do what I want:

Did you archive the solution with the plugin?

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

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.

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

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

1 Like

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:

  1. 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
  2. SELECT UNIX_TIMESTAMP(time) as time_sec, convert(100*value, double) as value, metrics as metric FROM test
  3. 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 :slight_smile:

Many thanks and greetings

Llewellyn

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.

Hi Paddy,

sorry, no answers until yet.

1 Like

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.

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