topic Eval round gives inconsistent half-rounding results in Archive
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433100#M77415
<P>I tried setting up a Splunk alert to check for inconsistencies between a rounded total and a raw total, but the alert has been giving false positives at random intervals because Splunk's <CODE>round</CODE> function doesn't seem to applying a consistent half-rounding rule. A clear example of this is if the following query is run:</P>
<PRE><CODE>*
| eval rounded_amount_22=round(22.275,2)
| eval rounded_amount_21=round(21.275,2)
| eval rounded_amount_20=round(20.275,2)
| eval rounded_amount_19=round(19.275,2)
| eval rounded_amount_18=round(18.275,2)
| eval rounded_amount_17=round(17.275,2)
| eval rounded_amount_16=round(16.275,2)
| eval rounded_amount_15=round(15.275,2)
| eval rounded_amount_14=round(14.275,2)
| eval rounded_amount_13=round(13.275,2)
| eval rounded_amount_12=round(12.275,2)
| eval rounded_amount_11=round(11.275,2)
| eval rounded_amount_10=round(10.275,2)
| head 1
| table
rounded_amount_10,
rounded_amount_11,
rounded_amount_12,
rounded_amount_13,
rounded_amount_14,
rounded_amount_15,
rounded_amount_16,
rounded_amount_17,
rounded_amount_18,
rounded_amount_19,
rounded_amount_20,
rounded_amount_21,
rounded_amount_22
</CODE></PRE>
<P>If Splunk was using half-up, half-down, or banker's rounding, I'd expect to see consistency in the results, but I just don't see a pattern in what I get back:</P>
<PRE><CODE>rounded_amount_10: 10.28
rounded_amount_11: 11.28
rounded_amount_12: 12.28
rounded_amount_13: 13.28
rounded_amount_14: 14.28
rounded_amount_15: 15.28
rounded_amount_16: 16.27
rounded_amount_17: 17.27
rounded_amount_18: 18.27
rounded_amount_19: 19.27
rounded_amount_20: 20.27
rounded_amount_21: 21.28
rounded_amount_22: 22.28
</CODE></PRE>
<P>In particular, every rounding scheme I know of should treat <CODE>10.275</CODE> and <CODE>20.275</CODE> the same - but as shown in the above list, Splunk is rounding <EM>up</EM> to <CODE>10.28</CODE> and <EM>down</EM> to <CODE>20.27</CODE>. I just don't see a pattern in it.</P>
<P>I've also tried wrapping the <CODE>round</CODE> function in <CODE>exact</CODE>, but that doesn't change the results.</P>
<P>What's going on here? Is it possible for this query to return consistent half-rounding results?</P>Mon, 22 Oct 2018 22:52:40 GMTpentwist2018-10-22T22:52:40ZEval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433100#M77415
<P>I tried setting up a Splunk alert to check for inconsistencies between a rounded total and a raw total, but the alert has been giving false positives at random intervals because Splunk's <CODE>round</CODE> function doesn't seem to applying a consistent half-rounding rule. A clear example of this is if the following query is run:</P>
<PRE><CODE>*
| eval rounded_amount_22=round(22.275,2)
| eval rounded_amount_21=round(21.275,2)
| eval rounded_amount_20=round(20.275,2)
| eval rounded_amount_19=round(19.275,2)
| eval rounded_amount_18=round(18.275,2)
| eval rounded_amount_17=round(17.275,2)
| eval rounded_amount_16=round(16.275,2)
| eval rounded_amount_15=round(15.275,2)
| eval rounded_amount_14=round(14.275,2)
| eval rounded_amount_13=round(13.275,2)
| eval rounded_amount_12=round(12.275,2)
| eval rounded_amount_11=round(11.275,2)
| eval rounded_amount_10=round(10.275,2)
| head 1
| table
rounded_amount_10,
rounded_amount_11,
rounded_amount_12,
rounded_amount_13,
rounded_amount_14,
rounded_amount_15,
rounded_amount_16,
rounded_amount_17,
rounded_amount_18,
rounded_amount_19,
rounded_amount_20,
rounded_amount_21,
rounded_amount_22
</CODE></PRE>
<P>If Splunk was using half-up, half-down, or banker's rounding, I'd expect to see consistency in the results, but I just don't see a pattern in what I get back:</P>
<PRE><CODE>rounded_amount_10: 10.28
rounded_amount_11: 11.28
rounded_amount_12: 12.28
rounded_amount_13: 13.28
rounded_amount_14: 14.28
rounded_amount_15: 15.28
rounded_amount_16: 16.27
rounded_amount_17: 17.27
rounded_amount_18: 18.27
rounded_amount_19: 19.27
rounded_amount_20: 20.27
rounded_amount_21: 21.28
rounded_amount_22: 22.28
</CODE></PRE>
<P>In particular, every rounding scheme I know of should treat <CODE>10.275</CODE> and <CODE>20.275</CODE> the same - but as shown in the above list, Splunk is rounding <EM>up</EM> to <CODE>10.28</CODE> and <EM>down</EM> to <CODE>20.27</CODE>. I just don't see a pattern in it.</P>
<P>I've also tried wrapping the <CODE>round</CODE> function in <CODE>exact</CODE>, but that doesn't change the results.</P>
<P>What's going on here? Is it possible for this query to return consistent half-rounding results?</P>Mon, 22 Oct 2018 22:52:40 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433100#M77415pentwist2018-10-22T22:52:40ZRe: Eval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433101#M77416
<P>Just tested something similar in Python and got similar results</P>
<P>x = round(15.275, 2)<BR />
print(x)<BR />
y = round(17.275, 2)<BR />
print(y)</P>
<P>produces</P>
<P>15.28<BR />
17.27</P>
<P>Is this down to Floating point inaccuracy?</P>Thu, 25 Oct 2018 15:47:41 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433101#M77416msivill_splunk2018-10-25T15:47:41ZRe: Eval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433102#M77417
<P>Playing around with this some more the following works</P>
<PRE><CODE>| makeresults
| eval rounded_amount_22=round(22.275*1000,-1)/1000
| eval rounded_amount_21=round(21.275*1000,-1)/1000
| eval rounded_amount_20=round(20.275*1000,-1)/1000
| eval rounded_amount_19=round(19.275*1000,-1)/1000
| eval rounded_amount_18=round(18.275*1000,-1)/1000
| eval rounded_amount_17=round(17.275*1000,-1)/1000
| eval rounded_amount_16=round(16.275*1000,-1)/1000
| eval rounded_amount_15=round(15.275*1000,-1)/1000
| eval rounded_amount_14=round(14.275*1000,-1)/1000
| eval rounded_amount_13=round(13.275*1000,-1)/1000
| eval rounded_amount_12=round(12.275*1000,-1)/1000
| eval rounded_amount_11=round(11.275*1000,-1)/1000
| eval rounded_amount_10=round(10.275*1000,-1)/1000
</CODE></PRE>
<P>The challenge here I think is the way computers hold floating numbers. <A href="http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm">http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm</A> gives some further background to the problem.</P>
<P>The above scales up the number to a whole number performs the round then reduces it back to a float.</P>Thu, 25 Oct 2018 15:58:59 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433102#M77417msivill_splunk2018-10-25T15:58:59ZRe: Eval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433103#M77418
<P>Here's a workaround... just add a tiny smidge to the thing being rounded.</P>
<PRE><CODE>| makeresults
| eval smidge=0.000000000001
| eval rounded_amount_16R=round(16.275,2)
| eval rounded_amount_15R=round(15.275,2)
| eval rounded_amount_16S=round(16.275+smidge,2)
| eval rounded_amount_15S=round(15.275+smidge,2)
| table smidge rounded_amount_*
</CODE></PRE>
<P>If you want to see output in table form, you can do this...</P>
<PRE><CODE> | untable smidge fieldname fieldvalue
| rex field=fieldname "(?<test>rounded_amount_\d\d)(?<method>\w)"
| chart first(fieldvalue) by test method
</CODE></PRE>Thu, 25 Oct 2018 17:21:26 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433103#M77418DalJeanis2018-10-25T17:21:26ZRe: Eval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433104#M77419
<P>Good explanation of the problem and a clear workaround based on that. I had figured this would probably be related to a floating-points somehow, but since PHP doesn't have this problem (but definitely is prone to floating-point number inaccuracies) I thought there might be another explanation... super interesting that Python gives the same results.</P>
<P>Regardless, using round with a negative value hadn't occurred to me, and provides a clear and relatively readable method of ensuring reliable results. Great stuff!</P>Thu, 25 Oct 2018 23:29:20 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433104#M77419pentwist2018-10-25T23:29:20ZRe: Eval round gives inconsistent half-rounding results
https://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433105#M77420
<P>Great, glad this works for you and thank you for the up vote.</P>Fri, 26 Oct 2018 08:13:47 GMThttps://community.splunk.com/t5/Archive/Eval-round-gives-inconsistent-half-rounding-results/m-p/433105#M77420msivill_splunk2018-10-26T08:13:47Z