Make average compare with two columns value based and display it in Panel

[alakdam]

I have two two columns of data, One is Expected box and another is Actual box.  I would like to make Percentage/Average of how much actual Box values are missing compare with Expected box.
Also some one my Actual boxes data there are no(which null/undefined)  value.  I would like to ignore those rows  where Actual boxes are  (null/undefined) which is compare with my Expected box. 

PS: I am beginner level splunker ?

Is there any way I can do this Average based on requirements?


This my search query:

index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=* |  spath path=data{}.actual_totes{}.finalBoxAmount output=actualBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | table estimatedBox actualBox

This my table looks like



PS: I would love to display that  Percentage of actual values missing in single panel, It would nice to show me, how to do that? This is wrong search query 👇🏾👇🏾👇🏾

    <panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=$orderNumber$ |  spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>

 

[richgalloway]

Try this query

index::service sourcetype::service "order_tote_analytics" 
| spath "data.order_number" | search "data.order_number"=* 
| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox
| fields estimatedBox actualBox
```Compute the % of missed boxes, ignoring empty actualBox entries```
| eval pctMissed=if(isnull(actualBox), null(), round((estimatedBox-actualBox) * 100 / estimatedBox, 2))
| table estimatedBox actualBox pctMissed
```Add the average of all pctMissed values on the end```
| appendpipe [ stats avg(pctMissed) as pctMissed | eval estimatedBox="Average Missed" ]

[alakdam]

Thank you very much again, I need in total average not another rows and column with average 😁

[alakdam]

Thank you very much. I am super new in splunk. Can you please show me In this panel ui, how to implement it.

    <panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" | spath "data.order_number" | search "data.order_number"=$orderNumber$ |  spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox| spath path=data{}.estimated_totes{}.box output=estimatedBox | stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>

This is how I want to show:

[richgalloway]

Thank you for clarifying the desired result.  This code should produce it.

<panel>
      <single>
        <title>Percentage of actual values missing</title>
        <search>
          <query>index::service sourcetype::service "order_tote_analytics" 
| spath "data.order_number" 
| search "data.order_number"=$orderNumber$ 
| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox
| eval pctMissing = (totalBox - finalBox) * 100 / finalbox
| table pctMissing</query>
          <earliest>$chosenTimePeriod.earliest$</earliest>
          <latest>$chosenTimePeriod.latest$</latest>
        </search>
        <option name="colorMode">block</option>
        <option name="drilldown">none</option>
        <option name="rangeColors">["0x53a051","0xf1813f","0xf8be34","0xf1813f","0xdc4e41"]</option>
        <option name="refresh.display">progressbar</option>
        <option name="useColors">1</option>
      </single>
    </panel>
    <panel>

[alakdam]

I am not getting  anything.  There are two finalBox you defined:  one we got as output=finalBox and another is sum(finalBox) as finalbox, is this the reason I am not getting anything.

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalbox
| eval pctMissing = (totalBox - finalBox) * 100 / finalbox
| table pctMissing

[richgalloway · Accepted solution]

There is a typo in my response ('finalbox' is not the same as 'finalBox').  Try this, instead.

| spath path=data{}.actual_totes{}.finalBoxAmount output=finalBox
| spath path=data{}.estimated_totes{}.box output=estimatedBox 
| stats sum(estimatedBox) as totalBox, sum(finalBox) as finalBox
| eval pctMissing = (totalBox - finalBox) * 100 / finalBox
| table pctMissing

[alakdam]

Thank you 🙏🏾🙏🏾🙏🏾.