Dashboards & Visualizations

Customize Order of Column Chart by sort_order Field

PReynoldsBitsIO
Explorer

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:

Screen Shot 2023-10-20 at 4.07.01 PM.pngIs there some other way to accomplish this?  

Labels (2)
0 Karma
1 Solution

PReynoldsBitsIO
Explorer

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

 

Screen Shot 2023-10-23 at 7.57.07 AM.png

 

View solution in original post

0 Karma

PReynoldsBitsIO
Explorer

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

 

Screen Shot 2023-10-23 at 7.57.07 AM.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @PReynoldsBitsIO ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...