How to change the color of columns on a bar chart based on a value?

[deanamite91]

I have the following search:

index="commercial_performance" $month_token$ Cat1="Efficiency Variance *" Value!="within *" |  stats sum(Value) AS Efficiency by Customer | sort + Efficiency

When the efficiency is < 0, I want to make the columns on the bar chart red, and when the efficiency is > 0, I want to make it green.

Here is the XML:

<panel>
      <title>Customer Efficiency</title>
      <chart>
        <search>
          <query>index="commercial_performance" $month_token$ Cat1="Efficiency Variance *" Value!="within *" |  stats sum(Value) AS Efficiency by Customer | sort + Efficiency </query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="charting.chart">bar</option>
        <option name="height">550</option>
        <option name="charting.fieldColors">{"redValue":0xFF0000,"greenValue":0x73A550}</option>
        <option name="charting.axisY2.enabled">undefined</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</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.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.scale">inherit</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.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.axisTitleY.text">Efficiency (£)</option>
      </chart>
    </panel>

[somesoni2 · Accepted solution]

Try like this

<panel>
       <title>Customer Efficiency</title>
       <chart>
         <search>
           <query>index="commercial_performance" $month_token$ Cat1="Efficiency Variance *" Value!="within *" |  stats sum(Value) AS Efficiency by Customer | sort + Efficiency | Efficiency_G=if(Efficiency>0,Efficiency,0) | eval Efficiency_R=if(Efficiency_G=0,Efficiency,0) | table Customer Efficiency_* </query>
           <earliest>0</earliest>
           <latest></latest>
         </search>
         <option name="charting.chart">bar</option>
         <option name="height">550</option>
         <option name="charting.fieldColors">{"Efficiency_R":0xFF0000,"Efficiency_G":0x73A550}</option>
         <option name="charting.axisY2.enabled">undefined</option>
         <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
         <option name="charting.axisLabelsX.majorLabelStyle.rotation">-45</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.scale">linear</option>
         <option name="charting.axisY.scale">linear</option>
         <option name="charting.axisY2.scale">inherit</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.sliceCollapsingThreshold">0.01</option>
         <option name="charting.chart.stackMode">stacked</option>
         <option name="charting.chart.style">shiny</option>
         <option name="charting.drilldown">all</option>
         <option name="charting.layout.splitSeries">0</option>
         <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
         <option name="charting.legend.placement">right</option>
         <option name="charting.axisTitleY.text">Efficiency (£)</option>
       </chart>
     </panel>

[deanamite91]

Apart from missing out an eval it worked perfectly!
Thank you!