How to sort data in chronological order by month, not alphabetically?

[JuliDeza]

Hello, in my query below I get the months in numerical format, I use a the chart command to obtain a chart divided into 12 months with values for different years. I would like to know how to show the months with their respective names, sorted chronologically. Thank you.

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval month_num=strftime(_time, "%m") 
| chart distinct_count(ticket_number) as "Cantidad tickets" by month_num Year

[JuliDeza]

Finally I solved it with this query:

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval Month=date_month 
| chart distinct_count(ticket_number) as "Cantidad tickets" by Month Year 
| eval orden = if(Month="january",1,if(Month="february",2,if(Month="march",3,if(Month="april",4,if(Month="may",5,if(Month="june",6,if(Month="july",7,if(Month="august",8,if(Month="september",9,if(Month="october",10,if(Month="november",11,12))))))))))) 
| sort orden 
| fields - orden

[micahkemp]

You could have also probably done | eval orden = stftime(_time, "%m")

[JuliDeza]

Thanks but that does not work, that way the months are sorted alphabetically.

[micahkemp]

I mean only swapping out line 5 in your search for the line I gave:

 source="test.csv"
 | eval Year=strftime(_time,"%Y") 
 | eval Month=date_month 
 | chart distinct_count(ticket_number) as "Cantidad tickets" by Month Year 
 | eval orden = strftime(_time, "%m")
 | sort orden 
 | fields - orden

[abhishekroy168]

these queries sorts data either by month or year.
My requirement is to sort by both month and year.
for eg May 2017,June 2018 , May 2018,July 2018,June 2019
Thanks in advance.

[493669]

Hi @JuliDeza,
try this:

source="test.csv"
 | eval Year=strftime(_time,"%Y") 
 | eval month=strftime(_time, "%B") 
 |sort - _time
 | chart distinct_count(ticket_number) as "Cantidad tickets" by month Year

[JuliDeza]

Thanks for your answer @493669. It also changes the graph completely, showing only two months of the last year. The query should show the same graph, but changing the numbers by the names of the months. For that, the chart command counts and divides the data by month number and paints the data for different years in different colors.
I had thought to use that query and then somehow change the numbers of the months by their corresponding name.
or
Use this other query:

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval month=strftime(_time, "%B") 
| chart distinct_count(ticket_number) as "Cantidad tickets" by month Year

And sort the months chronologically.