Average wrong box estimate- Why is my attempt wrong?

[alakdam]

I have total 17 orders.  Box Estimates is wrong 6 out of 17 orders. What is the average wrong box estimate in total?

This is my attempt who is wrong:

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes 
| spath path=data{}.estimated_totes{}.box  output=estimatedBoxes 
| eventstats count AS total
| eval box_missing=if(actualBoxes != estimatedBoxes, "YES", "NO") 
| eval average= (actualBoxes - estimatedBoxes) / total * 100
| table actualBoxes estimatedBoxes total box_missing average

[ITWhisperer]

What events are you using?

How is it wrong?

What were you expecting?

[alakdam]

Sorry If question is not clear. 

These are the data I get from my event 👇🏾👇🏾

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes 
| spath path=data{}.estimated_totes{}.box  output=estimatedBoxes 

Below splunk table image.  I have estimated order was right 11 time and 6 time was wrong. I'm curious to know the percentage of incorrect box estimations overall.?

[ITWhisperer · Accepted solution]

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes 
| spath path=data{}.estimated_totes{}.box  output=estimatedBoxes 
| eval box_missing=if(actualBoxes != estimatedBoxes, 1, 0) 
| stats count as total sum(box_missing) as missing
| eval percent_wrong = 100*missing/total

[alakdam]

I don't see any answer. 

[ITWhisperer]

Try with eventstats so you can see which events have been included

| spath path=data{}.actual_totes{}.finalBoxAmount output=actualBoxes 
| spath path=data{}.estimated_totes{}.box  output=estimatedBoxes 
| eval box_missing=if(actualBoxes != estimatedBoxes, 1, 0) 
| eventstats count as total sum(box_missing) as missing
| eval percent_wrong = 100*missing/total

[alakdam]

I started learning splunk yesterday. don't know the difference between spats and evenstats. 😄 

[ITWhisperer]

Essentially, event stats adds the calculated values as new fields to all the events without dropping any events, whereas stats replaces all the events with a single event containing just the calculated or group by fields

[alakdam]

[ITWhisperer]

Try including the other fields

| table actualBoxes estimatedBoxes total box_missing missing percent_wrong

[alakdam]

PS: I just want only TOTAL average of wrong estimate 

[ITWhisperer]

Your actualBoxes and estimatedBoxes have not extracted correctly (or there weren't any values for them in your events)

[alakdam]

How come it returns these 

[alakdam]

PS: Some of the actual values are null

[ITWhisperer]

You have changed the names of the output fields on the spath so they no longer match the field names used in the eval - you should try and be consistent

[alakdam]

I am really sorry for stupid mistake 🙏🏾. Now it shows the averages but why it shows  17 rows  in same result. Cant I make one column and one row

[ITWhisperer]

Change the eventstats back to stats (as I explained earlier)

[alakdam]

Thank you for stick with me 🙏🏾🙏🏾