Splunk Search
Highlighted

Why are the legend and stacking order of this bar chart opposites?

Engager

When creating a stacked bar chart and putting the legend definitions on top, the legend order is reverse of the stacking order for the bar chart.
Making it look like this
alt text

The legend and stacking order seems to be determined by the output order so when writing "fields name, a, b" it sorts it like on the picture and if i reverse a and b in the search the legend and the bar stacking order just swaps but legend and stacking order will still be opposite.

Is there any way i can match the legend order to the bar stacking order?

EDIT: Basically i'm trying to make it look like this instead (quick MS Paint fix, not actually fixed) alt text
So that the legend order is matching the stacking order (b first, a second)

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Maybe one of the solutions from this previous question would work for you?
https://answers.splunk.com/answers/475167/is-it-possible-to-customize-the-order-of-fields-in.html

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Engager

I have tried that, but it seems to relying on the alphabetical ordering. In my search the order seems to only be determined on the order in which they are listed after the field command.
So if i write "fields name, b, a" instead of "fields name, a, b" they will simply swap places both in the legend and stacking order.

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Splunk Employee
Splunk Employee

Hey @Rolthers, were you able to find a solution?

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Engager

No i gave up on it since it isn't a major problem. But if anyone else has an answer it would still be welcome.

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Legend

@Rolthers, can you share your current query.

I have mocked up some sampe data to come up with the stacked bar chart similar to what you have. The pipes before stats are just to mock some data.

Step 1) Stats get the total value for each field name
Step 2) Sort gets the data sorted descending or ascending as per need.
Step 3) Using accum sequence number seq is generated for sorted value.
Step 4) The name field is prefixed with sequence number using eval
Step 5) Transpose command is used to reverse the table to be presented as stacked bar chart.
Step 6) Use rename commands to remove sequence numbers from field names for presenting. (PS. If you can live with Field Names prefixed with sequence number, this step might not be required).

Following is one of the searches used in example above:

|  makeresults
|  eval name="a", value=123
|  append 
    [|  makeresults
    |  eval name="b", value=230]
|  append 
    [|  makeresults
    |  eval name="c", value=50]
|  stats sum(value) as value by name
|  sort - value
|  eval seq=1
|  accum seq
|  eval name=seq.".".name
|  fields - seq
|  transpose header_field=name
|  rename 1.* as *
|  rename 2.* as *
|  rename 3.* as *

alt text

Following is the complete run anywhere Dashboard code example:

<dashboard>
  <label>Sort Legends based on Value (Asc or Desc)</label>
  <row>
    <panel>
      <title>Sort Descending</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval name="a", value=123
|  append 
    [|  makeresults
    |  eval name="b", value=230]
|  append 
    [|  makeresults
    |  eval name="c", value=50]
|  stats sum(value) as value by name
|  sort - value
|  eval seq=1
|  accum seq
|  eval name=seq.".".name
|  fields - seq
|  transpose header_field=name
|  rename 1.* as *
|  rename 2.* as *
|  rename 3.* as *</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">bar</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Sort Ascending</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval name="a", value=123
|  append 
    [|  makeresults
    |  eval name="b", value=230]
|  append 
    [|  makeresults
    |  eval name="c", value=50]
|  stats sum(value) as value by name
|  sort value
|  eval seq=1
|  accum seq
|  eval name=seq.".".name
|  fields - seq
|  transpose header_field=name
|  rename 1.* as *
|  rename 2.* as *
|  rename 3.* as *</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">bar</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Engager

Thanks for the thorough answer.

My query is pretty much just "fields name, a, b" since i am just extracting some fields from my dataset and throwing them straight into the bar chart.

If i understood your answer correctly you are changing the sorting order causing both the stacking order and the legend order to change.

What i am looking for is a way to change the legend order without affecting the stacking order or the other way around. So with your example i would want the "Sort Ascending" bar chart but with the "Sort Descending" legend order. So that the first value in the stacked bar would also be the first value in the legend order when reading it from top to bottom.

Apologies if i misunderstood your answer.

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Legend

@Rolthers, yes you got the purpose of the query right. It sorts field names based on values and then displays the same in chart as well as legend as sorted. I thought that your concern was that Legends always sort based on their field names rather that the values of field names on which they are sorted. While stacking is the same in Column and Bar chart its sequence appears opposite in Bar chart. So, straightaway this is the default behavior of the visualization stacking and its legend.

If you want to have reverse sort for legends as compared to the chart there would have to be a crooked workaround. Since you have only three columns with fixed columns a and b, you can use CSS to access Legend node in DOM and reverse there position using translate. Let me know if my assumption is correct and I can provide the CSS (it might require tweaking based on your actual dashboard).




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Engager

You assume correctly.

I haven't worked much with CSS and never in relation to Splunk so an example would be much appreciated. I also have similar bar charts but with 3 (a, b, c) columns instead of 2 (a, b), would it also be applicable to those cases? With some modifications of course.

Feel free to post it as an answer, so i can accept it if it works.

0 Karma
Highlighted

Re: Why are the legend and stacking order of this bar chart opposites?

Legend

@Rolthers, are legends always on top of your chart as shown your mock screenshot?




| eval message="Happy Splunking!!!"


0 Karma