Sorting X axis by month

[mcwomble]

I have the following search which displays amounts of records by month (X-axis).

index="billing" suspededrecords | eval monthnum = if(month="JAN",1,if(month="FEB",2,if(month="MAR",3,if(month="APR",4,if(month="MAY",5,if(month="JUN",6,if(month="JUL",7,if(month="AUG",8,if(month="SEP",9,if(month="OCT",10,if(month="NOV",11,if(month="DEC",0,0)))))))))))) |sort num(monthnum) | chart max(monthsuspense) as "Suspense" by month

However the X-axis is always sorted month name alaphabetically which is confusing. I put in the the above eval and sort statements to try and create fields that would allow the months to be displayed in the correct order, but it doesn't work. I'm starting to think that I starting to complicate the issue and wondered if there is an easy way to achieve a correct listing of months.

[Jason · Accepted solution]

I wrestled with this for a bit when trying to aggregate a particular day of the week's values over a month, and display the "average week" for the month. I could count by date_wday, but it the x axis would always be sorted lexigraphically. Very confusing.

Here's my solution: use the eval to make a field with both a number for ordering as well as the name for easy viewing:

search ..... | eval Day = if(date_wday="sunday","1. Sunday",if(date_wday="monday","2. Monday",if(date_wday="tuesday","3. Tuesday",if(date_wday="wednesday","4. Wednesday",if(date_wday="thursday","5. Thursday",if(date_wday="friday","6. Friday",if(date_wday="saturday","7. Saturday",0)))))))| chart count over Day

[assaphmehr]

Jason,

Here's a cleaner eval that does the same:

... | eval Day=strftime(_time, "%u. %A") | ...

Hope this helps makes it more readable 🙂

[Paolo_Prigione]

I'd suggest you to use timechart, as you are plotting against time, and its bucketing options, to compute the max() over the entire month:

timechart span="1mon" max(monthsuspense) as Suspense

After your comment, I'm editing the answer...

I've noticed that you issued the chart command using "month" and not "monthnum" as X-axis. Was this intentional?

If noy, you might then want to:

1. replace the monthnumber generation with something more compact;
2. use the chart "over monthnum"

So that would end up as:

index="billing" suspededrecords | eval monthnum=strftime(strptime("2010/" + month + "/01", "%Y/%b/%d"),"%m") | chart max(monthsuspense) over monthnum span=1mon 

[mcwomble]

Unfortunately it doesn't seem to be as simple as creating a time chart. The data that generates this graph comes in once a day from a dbquery which has the amounts defined by month and is effectively a snapshot at one moment in time. If reprocessing takes place the historical amount per month can change. So really the month on the x-axis is just a field value rather than a distinct time event.