topic How to sort the display order of data inside a bar on a stacked bar chart in Splunk 6.2.1? in Splunk Search

How to sort the display order of data inside a bar on a stacked bar chart in Splunk 6.2.1?

rgsage — Thu, 10 Dec 2015 20:30:42 GMT

We have a search like this:

... 
| eval week_start=relative_time(_time,"@w")
| eval week_label=strftime(week_start, "Week of %m-%d")
| chart sum(activityTime) AS hours BY customer week_label

Which gives a table like this:

customer    Week of 11-29  Week of 12-06
--------    -------------  -------------
Customer-A              8             10
Customer-B             15              7

And a stacked bar chart that looks like this:

Customer-A [ 12-06    ][ 11-29  ]
Customer-B [ 12-06 ][ 11-29         ]

However, we want the data inside the bars to appear in date order as follows:

Customer-A [ 11-29  ][ 12-06    ]
Customer-B [ 11-29         ][ 12-06 ]

Using | sort I can change the order of customers but I can't budge the display order of the elements inside each bar. How can I change the elements inside the bar to appear in date (also happens to be alpha) order?

We are on 6.2.1

Re: How to sort the display order of data inside a bar on a stacked bar chart in Splunk 6.2.1?

yannK — Tue, 23 Feb 2016 00:00:23 GMT

The problem is that the chart will return the data with the columns headers sorted alphabetically,
_time week of 1-10, week of 1-17, week of 01-24 ...
but the visualization with stacked columns will put the last ones on the bottom,. and the firs ones on the top

A trick is to rename the title to add a number that will be sorted in the reverse order

index="_internal" admin source="*scheduler.log"  | eval week_start=relative_time(_time,"@w")
| eval week_number=strftime(week_start, "%U")
| eval year_number=strftime(week_start, "%Y")
| convert num(week_number) AS week_number num(year_number) AS year_number
| eval title_sort=10000-year_number-week_number
| eval week_label="(".title_sort.") ".strftime(week_start, "Week of %m-%d")
| eval hours=run_time/60/60
| chart sum(hours) by app week_label

Re: How to sort the display order of data inside a bar on a stacked bar chart in Splunk 6.2.1?

rgsage — Tue, 29 Sep 2020 08:53:54 GMT

Thank you. Since I am constrained to column-name alphabetical sort order inside the bar I ended up doing it like this (our search goes back up to 4 weeks which explains the magic 4 in eval title_sort below):

... earliest=@w-3d
...
| eval week_start=relative_time(_time,"@w")
| eval sort_start=relative_time(now(),"@w-3w")
| eval title_sort=4-round((week_start-sort_start) / (60*60*24*7), 0)
| eval week_label="(".title_sort.") ".strftime(week_start, "Week of %m-%d")
...

Which gives column titles like this that sort chronologically (reverse alphabetically) inside the bars:

(1) Week of 12-06
(2) Week of 11-29
...

The title_sort index numbers are just a bit more palatable that the numbers generated by 10000-year_number-week_number.