topic Re: How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)? in Splunk Search

How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

Vijaikanth — Fri, 29 May 2015 07:12:22 GMT

Requirement: We need the order of Month column names to start with the three letter month followed by year in ascending order.

Request Name    Nov-2014    Dec-2014    Jan-2015    Feb-2015    Mar-2015
Get                50          10          20          20          40
Post               15          5           45          23          34

However, while doing the Pivot, the columns are getting sorted by the string value in alphabetical order and the output is:

Request Name    Dec-2014    Feb-2015    Jan-2015    Mar-2015    Nov-2014
Get                10          20          20          40          50
Post                5          23          45          34          15

How do i rearrange the columns to point in ascending order.

Re: How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

Vijaikanth — Fri, 29 May 2015 07:44:01 GMT

Here Month = strftime(strptime(Date,"%d/%m/%Y"),"%b-%Y")

so Month = "Apr-2015"

When i set Month = strftime(strptime(Date,"%d/%m/%Y"),"%m-%Y")

so Month = "04-2015" and it will sort the columns properly but again i need to rename the "04-2015" to "Apr-2015" like that for all dynamic columns for the month

Re: How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

MuS — Sat, 30 May 2015 22:09:26 GMT

And that's the way you should do it, Splunk is not aware that Feb is equal the 02 months and sorts it based on the alphanumeric value. If you need to sort it like this, you provided the answer yourself 😉

Re: How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

Vijaikanth — Sun, 31 May 2015 16:44:57 GMT

Yes. If we set the Month as "04-2015" instead of "Apr-2015" we can able to sort it. But after that how to rename the value

"04-2015" to "Apr-2015".

Is it possible to apply wildcard characters in the column names? like Rename 04* as Apr (or) doing some substring on the Column name and rename ?

Re: How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

Vijaikanth — Thu, 16 Jul 2015 07:32:53 GMT

Finally Solved the chronological order in the Format

Month = strftime(strptime(Date,"%d/%m/%Y"),"%Y-%m (%b)")

so the Result will be 2015-01 (Jan), 2015-02 (Feb).....

It Works fine for all year, all month...