Need helping / tips / advice in drawing line/scatter graphs, year by year lines

Grafana Dashboards
1

I am very new to Grafana. Using v9.3 on Mac and my data in MySQL.

I had all my data in one massive raw table, with date/time and value column, I used SQL and procedures to turn that table in pivot like table, see attached (i turned to pivot table, i read somewhere Grafana like data in that style).

graph

I have trying for past day(s) to draw line or scatter graph, each year separate colour line (all in one single graph).

Please help.

After starting the post, this is the best, I have got so far, I would like something like this all one one chart, Y axis is fine, X axis to to be Jan, Feb… Dec, Line or scatter each for year, to say 2022 (red), 2021 (green), 2020 (blue) … etc.

best_so_far
best_so_far925×303 12.2 KB

SQL for Grafana chart:

select * from full_p_data;

SQL for table above DDL/DML

CREATE TABLE `full_p_data` (
  `YEAR` varchar(4) DEFAULT NULL,
  `Jan` decimal(42,0) DEFAULT NULL,
  `Feb` decimal(42,0) DEFAULT NULL,
  `Mar` decimal(42,0) DEFAULT NULL,
  `Apr` decimal(42,0) DEFAULT NULL,
  `May` decimal(42,0) DEFAULT NULL,
  `Jun` decimal(42,0) DEFAULT NULL,
  `Jul` decimal(42,0) DEFAULT NULL,
  `Aug` decimal(42,0) DEFAULT NULL,
  `Sep` decimal(42,0) DEFAULT NULL,
  `Oct` decimal(42,0) DEFAULT NULL,
  `Nov` decimal(42,0) DEFAULT NULL,
  `Dec` decimal(42,0) DEFAULT NULL
);

-- Data for table

INSERT INTO `full_p_data` VALUES ('2015',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,11,19,6),('2016',17,26,17,10,21,13,10,18,19,15,12,10),('2017',20,18,23,19,20,16,11,9,10,12,14,8),('2018',20,17,17,6,18,21,11,10,14,9,9,5),('2019',20,16,37,30,25,12,11,8,8,5,5,10),('2020',9,2,6,16,4,6,17,11,15,7,9,26),('2021',16,18,16,9,5,10,6,4,8,6,16,5),('2022',11,8,6,5,19,10,20,9,17,8,22,3);

Thank You very much any help.

Info:

  • What Grafana version and what operating system are you using?
    9.3

  • What are you trying to achieve?
    Draw scatter or line graph (year by year lines), X axis have Month, X axis have the numbers qty, each line on graph be different colour.

  • How are you trying to achieve it?
    SQL

  • What happened?
    Only 1 line, by i want line for each year

  • What did you expect to happen?
    8 lines

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

Welcome

Could you please provide dml and ddl for your data? It helps us help you because we can have your data in our mysql. Hard to do that from an image

:wink:

3

Thank You very much for getting back. I have edited my original post to include code to test. My original data was like this below, before I turned my data into pivot table like data (for Grafana purpose). Now sure, if my raw data makes it easy for the graph.

CREATE TABLE `full_sales_log` (
  `YEAR` varchar(4) DEFAULT NULL,
  `MONTH` varchar(2) DEFAULT NULL,
  `c_val` bigint(21) NOT NULL
) 

INSERT INTO `full_sales_log` VALUES ('2015','09',5),('2015','10',11),('2015','11',19),('2015','12',6),('2016','01',17),('2016','02',26),('2016','03',17),('2016','04',10),('2016','05',21),('2016','06',13),('2016','07',10),('2016','08',18),('2016','09',19),('2016','10',15),('2016','11',12),('2016','12',10),('2017','01',20),('2017','02',18),('2017','03',23),('2017','04',19),('2017','05',20),('2017','06',16),('2017','07',11),('2017','08',9),('2017','09',10),('2017','10',12),('2017','11',14),('2017','12',8),('2018','01',20),('2018','02',17),('2018','03',17),('2018','04',6),('2018','05',18),('2018','06',21),('2018','07',11),('2018','08',10),('2018','09',14),('2018','10',9),('2018','11',9),('2018','12',5),('2019','01',20),('2019','02',16),('2019','03',37),('2019','04',30),('2019','05',25),('2019','06',12),('2019','07',11),('2019','08',8),('2019','09',8),('2019','10',5),('2019','11',5),('2019','12',10),('2020','01',9),('2020','02',2),('2020','03',6),('2020','04',16),('2020','05',4),('2020','06',6),('2020','07',17),('2020','08',11),('2020','09',15),('2020','10',7),('2020','11',9),('2020','12',26),('2021','01',16),('2021','02',18),('2021','03',16),('2021','04',9),('2021','05',5),('2021','06',10),('2021','07',6),('2021','08',4),('2021','09',8),('2021','10',6),('2021','11',16),('2021','12',5),('2022','01',11),('2022','02',8),('2022','03',6),('2022','04',5),('2022','05',19),('2022','06',10),('2022','07',20),('2022','08',9),('2022','09',17),('2022','10',8),('2022','11',22),('2022','12',3);
1 Like