Hi,
I am reading data from a csv file using a lookup.
I need to plot monthly transaction charge volume on a bar chart as follows:
Unfortunately when I sort, Splunk uses alphabetical order to arrange the months.
Is there a way to have these months sorted in a chronological order as : Jan-17, Feb-17... and so on ?
The search I am using is :
|inputlookup ChargeVolume.csv |stats sum(Charge Volume (USD)) as "Charge Volume" , sum(Pre Auth volume) as "Pre Auth Volume" by "Reporting Month-YEAR" | Rename "Reporting Month-YEAR" to "Month"
try something like this,
|inputlookup ChargeVolume.csv |stats sum(Charge Volume (USD)) as "Charge Volume" , sum(Pre Auth volume) as "Pre Auth Volume" by "Reporting Month-YEAR" | Rename "Reporting Month-YEAR" to "Month" | eval rank=case(Month like "Jan-%",1,Month like "Feb-%",2,Month like "Mar-%",3,Month like "Apr-%",4,Month like "May-%",5,Month like "Jun-%",6,Month like "Jul-%",7,Month like "Aug-%",8,Month like "Sep-%",9,Month like "Oct-%",10,Month like "Nov-%",11,Month like "Dec-%",12,1=1,13) | sort 0 rank | fields - rank
Thanks again Vasanth 🙂
This one works !
try something like this,
|inputlookup ChargeVolume.csv |stats sum(Charge Volume (USD)) as "Charge Volume" , sum(Pre Auth volume) as "Pre Auth Volume" by "Reporting Month-YEAR" | Rename "Reporting Month-YEAR" to "Month" | eval rank=case(Month like "Jan-%",1,Month like "Feb-%",2,Month like "Mar-%",3,Month like "Apr-%",4,Month like "May-%",5,Month like "Jun-%",6,Month like "Jul-%",7,Month like "Aug-%",8,Month like "Sep-%",9,Month like "Oct-%",10,Month like "Nov-%",11,Month like "Dec-%",12,1=1,13) | sort 0 rank | fields - rank
Good one. for safer side use year also in the sorting ,
|inputlookup ChargeVolume.csv |stats sum(Charge Volume (USD)) as "Charge Volume" , sum(Pre Auth volume) as "Pre Auth Volume" by "Reporting Month-YEAR" | Rename "Reporting Month-YEAR" to "Month" | eval rank=case(Month like "Jan-%",1,Month like "Feb-%",2,Month like "Mar-%",3,Month like "Apr-%",4,Month like "May-%",5,Month like "Jun-%",6,Month like "Jul-%",7,Month like "Aug-%",8,Month like "Sep-%",9,Month like "Oct-%",10,Month like "Nov-%",11,Month like "Dec-%",12,1=1,13) |rex field=Month "-(?<rank_year>\d+)" | sort 0 rank_year, rank| fields - rank rank_year
did your try your search | sort Month
Hey yea I did that.
It simply sorts the months by alphabetical order and not chronologically