topic Re: Customize Order of Column Chart by sort_order Field in Dashboards & Visualizations

Customize Order of Column Chart by sort_order Field

PReynoldsBitsIO — Fri, 20 Oct 2023 21:09:05 GMT

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?

Re: Customize Order of Column Chart by sort_order Field

gcusello — Sat, 21 Oct 2023 09:49:53 GMT

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

Re: Customize Order of Column Chart by sort_order Field

ITWhisperer — Sat, 21 Oct 2023 09:57:20 GMT

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

Re: Customize Order of Column Chart by sort_order Field

PReynoldsBitsIO — Mon, 23 Oct 2023 12:45:41 GMT

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

Re: Customize Order of Column Chart by sort_order Field

PReynoldsBitsIO — Mon, 23 Oct 2023 12:51:31 GMT

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

Re: Customize Order of Column Chart by sort_order Field

PReynoldsBitsIO — Mon, 23 Oct 2023 12:58:07 GMT

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

Re: Customize Order of Column Chart by sort_order Field

gcusello — Mon, 23 Oct 2023 14:13:29 GMT

Hi @PReynoldsBitsIO ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

Re: Customize Order of Column Chart by sort_order Field

Aufex — Thu, 16 Jan 2025 13:24:40 GMT

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.