Chart not obeying charting.axisY.includeZero=false

[phoenixdigital]

Hi All,

I've been trying to get a simple XML chart to not include zero on the Y Axis but it appears to be broken. No matter what I do the Y Axis always includes zero.
http://imgur.com/a/dECma

There was a previous question in Answers from 2012 on this and the setting appears to have moved from

charting.chart.axisY.includeZero

to

charting.axisY.includeZero

Ref: https://answers.splunk.com/answers/23894/shrink-y-axis-range.html

However I've tried everything and just can't get it to work even in the most simple of examples.

<dashboard>
  <label>Zero Value Test</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal earliest=-3h | timechart count | where count > 500</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisY.includeZero">0</option>
        <option name="charting.axisY.minimumNumber">auto</option>
        <option name="charting.chart">area</option>
      </chart>
    </panel>
  </row>
</dashboard>

charting.axisY.minimumNumber works just fine but I really want the lower point to be set to the minimum value in the result set.

I've tried all combinations of using 0, False and false. The Splunk GUI always changes it to 0 after saving in the editor.

Documentation here: https://docs.splunk.com/Documentation/Splunk/6.6.0/Viz/ChartConfigurationReference

Edit: Thanks for the initial replies everyone. I did manage to fix that original basic example by removing the auto as cmerriman suggested. However it doesn't work with my chart as shown in the image in the first link of this post.

So I have extended the "test" query to simulate the style of data I am actually working with. This is not the real search I am working with it is just a way to post here so that everyone can try it on their Splunk instance. You may want to tweak the executes > and < filters to get a range that is not too large for the sample data. Also you will need to html encode the > and < in the query on a dashboard (Answers removes them when I submit the edit)

<dashboard>
  <label>Zero Value Test</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal earliest=-24h latest=-1h sourcetype=splunkd group=pipeline, name=parsing executes<1000  executes>500
| eval TIME_KEY = strftime(_time, "%H")
| stats avg(executes) AS average  perc90(executes) as perc_60 perc30(executes) as perc_30 by TIME_KEY
| rename _time AS Time
| eval _lower = "perc_30"   
| eval _upper = "perc_60" 
| eval _predicted = "average"</query>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisY.includeZero">0</option>
        <option name="charting.chart">line</option>
      </chart>
    </panel>
  </row>
</dashboard>

Results still include zero as shown below
http://imgur.com/a/2ygpL

[woodcock]

Try hard-coding it like this:

<dashboard>
  <label>Zero Value Test</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal earliest=-3h | timechart count | where count > 500</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisY.minimumNumber">500</option>
        <option name="charting.chart">area</option>
      </chart>
    </panel>
  </row>
</dashboard>

I am assuming that your 500 is set by token and you should be able to use the same token inside of the minimumNumber line, too.

[phoenixdigital]

Thanks for the response.

That was just a test query I built since I knew nobody had my data. Everyone has _internal so I wrote that search to simulate the data I am working with.

I have editted the original post with a search pretty much simulating the data and visualisation I am working with.

Still not working for me.

[cmerriman · Accepted solution]

i have this:

<dashboard>
  <label>Zero Value Test</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal earliest=-3h | timechart count | where count > 500</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisY.includeZero">0</option>
        <option name="charting.chart">line</option>
      </chart>
    </panel>
  </row>
</dashboard>

and it seems to have fixed the issue. try to remove the auto minimum value.

[phoenixdigital]

Thanks for the response.

Removing that from the basic test did in fact work. However it didn't work on my real search with my own data.

I have editted the original post with a search pretty much simulating the data and visualisation I am working with and removing auto minimum value doesn't work sadly.

[cmerriman]

this is what i've done with the new syntax you provided, and it seems to work. you could probably do some tweaking to change the interval of the axis. i made it a base search to create a token for the min and max values (subtracting and adding 5 points off each for a boundary) and used the token in the chart option.

<dashboard script="showtokens.js">
   <label>Zero Value Test</label>
    <search id="base">
           <query>index=_internal earliest=-24h latest=-1h sourcetype=splunkd group=pipeline name=parsing executes&lt;1000  executes&gt;500
 | eval TIME_KEY = strftime(_time, "%H")
 | stats avg(executes) AS average  perc90(executes) as perc_90 perc30(executes) as perc_30 by TIME_KEY
 | rename _time AS Time
 | eval _lower = "perc_30"   
 | eval _upper = "perc_90" 
 | eval _predicted = "average"|eventstats max(perc_90) as max min(perc_30) as min|eval max=max+5|eval min=min-5</query>
            <sampleRatio>1</sampleRatio>
            <done>
             <set token="maxaxis">max</set>
             <set token="minaxis">min</set>
            </done>
         </search>
   <row>
     <panel>
       <chart>
         <search base="base">
           <query>table TIME_KEY average perc_90 perc_30</query>
               </search>
         <option name="charting.axisY.includeZero">0</option>
         <option name="charting.chart">line</option>
         <option name="charting.axisX.minimumNumber">$minaxis$</option>
         <option name="charting.axisX.maximumNumber">$maxaxis$</option>
       </chart>
     </panel>
   </row>
 </dashboard>

[phoenixdigital]

Thank you. I like your thinking!!!!

That works perfectly with a few tweaks but you got me on the right track.
http://imgur.com/a/QdEMp

Here is the actual dashboard in all its glory. Essentially it is charting the average speed for traffic on freeways over the last 6 months with percentiles to give an idea of variability then comparing to today traffic speeds.

<dashboard stylesheet="liveworm.css" refresh="300">
  <label>Foyer 1 - Live Freeway Speeds</label>
  <description>* Upper and lower bands indicate the 95th and 5th percentile of the average speed.</description>
  <search id="base">
    <query>| tstats count AS "Count of BluetoothLinks" avg(BluetoothLinks.speed) AS speed from datamodel=Customer_Bluetooth_Averaged.BluetoothLinks where (nodename = BluetoothLinks) (BluetoothLinks.Freeway="Y") (BluetoothLinks.WORKDAY_FLG= "Y ") (BluetoothLinks.bluetooth_id="*") (BluetoothLinks.date_hour&gt;=5) (BluetoothLinks.date_hour&lt;=20) groupby BluetoothLinks.TIME_KEY_30M, BluetoothLinks.DATE_KEY 
| rename BluetoothLinks.TIME_KEY_30M AS TIME_KEY 
| rename BluetoothLinks.DATE_KEY AS DATE_KEY 
| fields TIME_KEY, DATE_KEY, speed 
| stats avg(speed) AS "Average speed" perc95(speed) AS "95th Percentile speed" perc5(speed) AS "5th Percentile speed" by TIME_KEY 
| join type=left TIME_KEY 
    [ search index=Customer* sourcetype=json_bluetooth_links bluetooth_id="*" Freeway="Y" earliest=@d latest=now 
    | eval speed = (length/1000)/(travel_time/60) 
    | eval timeSegmentMin=floor(date_min/30) * 30 
    | eval timeSegmentMin=if(length(timeSegmentMin)&lt;2,"0"+timeSegmentMin,timeSegmentMin) 
    | eval TIME_KEY = date_hour + ":" + timeSegmentMin 
    | eval today_metric = if (strftime(time(), "%Y%m%d") == strftime(_time, "%Y%m%d"), speed, NULL) 
    | fields today_metric, speed, name, bluetooth_id, _time, TIME_KEY 
    | stats avg(today_metric) as "Today speed" by TIME_KEY ] 
| eval _lower = "5th Percentile speed" 
| eval _upper = "95th Percentile speed" 
| eval _predicted = "Average speed" 
| rename TIME_KEY AS "Time of Day" 
| eventstats min("5th Percentile speed") as min max("95th Percentile speed") as max |eval max=max+5 |eval min=min-5</query>
          <earliest>-6mon</earliest>
          <latest>@day</latest>
             <done>
              <set token="maxaxis">$result.max$</set>
              <set token="minaxis">$result.min$</set>
             </done>        
  </search>
  <row>
    <panel>
      <chart id="liveworm">
          <title>Min $minaxis$ and Max $maxaxis$</title>

        <search base="base">
            <query>fields "Time of Day", "Average speed", "5th Percentile speed", "95th Percentile speed", "Today speed", _lower, _upper, _predicted</query>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">-90</option>
        <option name="charting.axisY.includeZero">0</option>
        <option name="charting.chart">line</option>
        <option name="charting.axisY.minimumNumber">$minaxis$</option>
        <option name="charting.axisY.maximumNumber">$maxaxis$</option>
        <option name="charting.chart">line</option>
        <option name="charting.fieldColors">{"Today speed": 0xE51010, "Average speed": 0x1E93C6, "Median speed":0x9DD1F9}</option>
        <option name="height">750</option>
      </chart>
    </panel>
  </row>
</dashboard>

[cmerriman]

very cool, glad i could help!

[rjthibod]

I am seeing the same behavior as you.

My guess is Splunk's charting algorithm prioritizes other settings and default assumptions about desired chart output. Said another way, Splunk probably doesn't use charting.axisY.includeZero=false as an explicit indication to not use zero - it uses best efforts based on the axis construction algorithm.

Like I said, just a guess.

[phoenixdigital]

Yeah I think you are right. cmerriman did find a solution to my simple test but in my edited post I have a more accurate search simulating the data I am working with and cmerriman's fix doesn't work on that.