Customize Order of Column Chart by sort_order Field

[PReynoldsBitsIO]

I'm working on a column chart visualization that show income ranges:

"$24,999 and under"
"$25,000  - $99,999"
"$100,000 and up"

The problem is that when the column chart orders them, it puts "$100,000 and up" first instead of last. 

I've created an eval that assigns a sort_order value based on the field value that orders them correctly.  However, I can't figure out how to get the column chart to sort according to that field. 

This is what I'm currently trying:

| eval sort_order=case(income=="$24,000 and under",1,income=="$25,000 - $39,999",2,income=="$40,000 - $79,999",3,income=="$80,000 - $119,999",4,income=="$120,000 - $199,999",5,income=="$200,000 or more",6)
| sort sort_order
| chart count by income

Here's the visualization:

Is there some other way to accomplish this?  

[PReynoldsBitsIO · Accepted solution]

I wound up coming up with a solution.  Any spaces at the start of the field will be truncated when Splunk builds that chart.  I made a sort_order field that adds spaces to the start of the field value.  The more spaces, the earlier in the chart order the field is placed.

Here's the code now:

<Base Search>
| eval sort_order=case(
    income=="$24,000 and under","     $24,000 and under",
    income=="$25,000 - $39,999","    $25,000 - $39,999",
    income=="$40,000 - $79,999","   $40,000 - $79,999",
    income=="$80,000 - $119,999","  $80,000 - $119,999",
    income=="$120,000 - $199,999"," $120,000 - $199,999",
    income=="$200,000 or more","$200,000 or more")
| chart count by sort_order

[ITWhisperer]

Try this way round

| chart count by income
| eval sort_order=case(income=="$24,000 and under",1,income=="$25,000 - $39,999",2,income=="$40,000 - $79,999",3,income=="$80,000 - $119,999",4,income=="$120,000 - $199,999",5,income=="$200,000 or more",6)
| sort sort_order
| fields - sort_order

[PReynoldsBitsIO]

This didn't work.  The chart doesn't respond to the sort order.  Thanks for the attempt though.

[gcusello]

Hi @PReynoldsBitsIO,

if income field has fixed values (how it seems) you could use something like this:

<your_search>
| eval 
   income=case(income="$24,000 and under","1$24,000 and under",
   income="$25,000 - $39,999","2$25,000 - $39,999",
   income="$40,000 - $79,999","3$40,000 - $79,999",
   income="$80,000 - $119,999","4$80,000 - $119,999",
   income="$120,000 - $199,999","5$120,000 - $199,999",
   income="$200,000 or more","6$200,000 or more")
| chart count by income
| rename 
   "1$24,000 and under" AS "$24,000 and under"
   "2$25,000 - $39,999" AS "$25,000 - $39,999"
   "3$40,000 - $79,999" AS "$40,000 - $79,999"
   "4$80,000 - $119,999" AS "$80,000 - $119,999"
   "5$120,000 - $199,999" AS "$120,000 - $199,999"
   "6$200,000 or more" AS "$200,000 or more"

Ciao.

Giuseppe

[PReynoldsBitsIO]

Sadly, this didn't work.  The rename won't change the column values.  I have found a solution though, thank you.

[gcusello]

Hi @PReynoldsBitsIO ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[Aufex]

Hi,

after your basic search you can create a table. Then you can use replace  like | replace blub with 1blub ...

Then you create a chart  and do a rename after.