Help needed to create flex query

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?
    9.4.0 on docker container

  • What are you trying to achieve?
    write a flex query to create a bar graph

  • How are you trying to achieve it?
    i need to compare monthly averages between two annual periods. (ie 2021 vs 2022 monthly averages as bar graphs.
    x axis must be like example JAN and two bars with 2020, 2021 avg values

I need a help on how to create this query in grafana.
Thanks,
Surendra

2

I made it with GLPI data.
using a matrix transform
image

and a bar chart

image
image950×278 14.5 KB

My code is :
select
case
when year (gt.date_creation)=year(current_date())-1 then year(current_date())-1
when year (gt.date_creation)=year(current_date()) then year(current_date())
end Annee,
case
when month(gt.date_creation)=1 then “Janv”
when month(gt.date_creation) =2 then “Fev”
when month(gt.date_creation) =3 then “Mars”
when month(gt.date_creation) =4 then “Avr”
when month(gt.date_creation) =5 then “Mai”
when month(gt.date_creation) =6 then “Juin”
when month(gt.date_creation) =7 then “Juil”
when month(gt.date_creation) =8 then “Aou”
when month(gt.date_creation) =9 then “Sept”
when month(gt.date_creation) =10 then “Oct”
when month(gt.date_creation) =11 then “Nov”
when month(gt.date_creation) =12 then “Dec”
end “Mois”,
count(gt.id) as nbre
from glpi_tickets gt
where gt.is_deleted=0
and gt.entities_id =0
and year(gt.date_creation) >= year(current_date())-1
group by 1, month(gt.date_creation)
order by 1

3

the
case
when year (gt.date_creation)=year(current_date())-1 then year(current_date())-1
when year (gt.date_creation)=year(current_date()) then year(current_date())
end Annee,
is not needed, just year(gt.date_creation) as Annee