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!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...