How to create a bar chart that will stack values of given max value

[limjophilip]

Hi,
I want to create a bar chart that will stack values of given max value.

So the max value will be the max value for the X-Axis for the bar graph and the other values/field will be the stack value for the max value of the same row the y-axis will be the APPLICATION Type

What I currently have is that the bar graph is just stacking the total values of each field. What I need is to have the value fields (Value 1 - 3) to be shown as a portion of the bar graph for the Max Value field

Thank you in advance

[niketn · Accepted solution]

@limjophilip why dont you create a Chart Overlay with Max Value as Overlay field?

Following is a Simple XML run anywhere example:

<dashboard>
  <label>Chart Overlay with Max value</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults 
| eval application="FSD", val_1=1, val_2=4839, val_3=5000 
| append 
    [| makeresults 
    | eval application="ABC", val_1=501, val_2=1000, val_3=3215] 
| append 
    [| makeresults 
    | eval application="ASD", val_1=654, val_2=3243, val_3=123] 
| append 
    [| makeresults 
    | eval application="HYR", val_1=2343, val_2=478, val_3=4328]
| fields - _time
| eval MaxValue=10000
| table application MaxValue val_1 val_2 val_3</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">column</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.overlayFields">MaxValue</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">minimal</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>

[grittonc]

I'm having a hard time getting an image to upload, but I think this is easy to do if you calculate the difference between the max value and the other three. I edited my earlier answer to add the max_val column and an addtotals step that adds anything named like val_ so that you don't have to do a huge eval statement if you have a lot of column names.

The first four lines are just creating data. You should be able to apply the rest of it to your data.

| makeresults | eval application="FSD", val_1=1, val_2=4839, val_3=5000, max_val=12000
| append    [| makeresults | eval application="ABC", val_1=501, val_2=1000, val_3=3215, max_val=8000]
| append    [| makeresults | eval application="ASD", val_1=654, val_2=3243, val_3=123, max_val=10000]
| append    [| makeresults | eval application="HYR", val_1=2343, val_2=478, val_3=4328, max_val=12000]
| sort -max_val
| addtotals fieldname=subtotal val_*
| eval diff=max_val-subtotal | table  application, diff, val*

[limjophilip]

Thanks, this is the idea I'm looking for but I need something that will be used for large number of rows, 50-100 rows

[limjophilip]

Thanks a lot, how can I use this in multiple rows? in the makeresults the application is indicated. How can I use select all not a specific application?

[grittonc]

The makeresults statements are just creating data. If you run just the first four lines you should see something similar to the data posted in your screenshot.

_time               application     max_val val_1   val_2   val_3
2019-08-06 07:53:42 FSD             12000   1       4839    5000
2019-08-06 07:53:42 ABC             8000    501     1000    3215
2019-08-06 07:53:42 ASD             10000   654     3243    123
2019-08-06 07:53:42 HYR             12000   2343    478     4328

The last 3 lines should work with any dataset that has fields named max_val, application, and multiple val_* fields. Is it not working?

[limjophilip]

thanks a lot I got what I wanted!

[grittonc]

You can make this work for many rows. Do they have the same max value?

[limjophilip]

They don't have the same max value. Can you share how can I do that?

[grittonc]

I updated my answer to show how this can work for records that don't have the same max value.