Rounding Problem

[MsBindy]

I'm having trouble figuring out why my formula is not rounding as intended.

I am trying to round the cost rate to 2 places;  then multiply the rounded cost rate times the hours.

Seems pretty simple,  but I keep ending up one penny off.

[patrick_digan · Accepted solution]

@MsBindy This issue has confounded me as well in the past. If you change your calculated field to fixed decimal with a lot of decimals (I used size 200.16) then you can see that it's internally storing it as a slightly small number than 237.895:

This can create problems for your rounding as you've noted. Note that I had workarounds in pre 11.0 versions but those loopholes have since been closed.

[DanS]

Wow, that was definitely giving me some trouble. Thanks for pointing that out Patrick. 

[fharper]

I hit the same issue building a recon tool for a billing system.  I believe the issue is in choice of rounding rules.  I think it became an issue when done In-DB because you are using rounding rules in your back end DB which is configured differently from Alteryx.  In my own situation our billing system uses Round_Half_up rule and Alteryx seems to use Round_Half_even thus creating a discrepancy at times.

Like you I have to calculate a daily rate when rates change mid-month and prorate.  The billing system will round up at times when Alteryx will round down.  

Extract from IBM

ROUND_HALF_UP - this is what most servers, mainframes and DBs are using in my experience

Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is greater than, or equal to, 0.5; otherwise, behaves as for ROUND_DOWN. This is the rounding mode that is typically taught in schools.

ROUND_HALF_DOWN

Round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. Behaves as for ROUND_UP if the discarded fraction is greater than 0.5; otherwise, behaves as for ROUND_DOWN.

ROUND_HALF_EVEN - This is what Alteryx seems to use

Round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for ROUND_HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND_HALF_DOWN if it is even. This is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations, and is sometimes referred to as Banker's rounding.

I am in the middle of discussing this with Alteryx and so have not had them confirm my investigation but all the data points to this as I have examples of the various values behaving according to this belief, aka the facts support this.

I am using a double round approach to  the calculation of rates which has reduced the incidence significantly yet there remains the rare occurrence so I am looking at a more involved manual rounding method to insure Round_Half_up results.

Unfortunately this is compounded by another issue I found along the way.  

In my formula to define the rate it is defined as fixed 2 decimal.  Originally I used a single round.  The underlying calculated value for example was 2.1756 and was rounded to 2.175 but shows as 2.18 in the preview of the formula and the field is defined as 2 decimal so I am assuming I am storing 2.18 and that is what subsequent formula using this rate will see or the double value most close to 2.18, Alteryx will tell you they convert everything to Double format to perform calculations.

However when I use the rate it calculates out as 2.175, not 2.18 or the double equivalent which also is 2.18.  So if you use fixed decimal data types when creating fields it quite likely is a value other than that displayed by preview.  My work around is double rounding, to 3 decimal then to 2 decimal.  My point here is unless someone has experience in this it is easy for many to trust the preview and see the value they expect and not realize they have to tweak their formula a bit more to insure it is what shows in preview.

This too I have raised with Alteryx support.

So the big thing for the original issue of thepost is Alteryx uses ROUND_HALF_EVEN and many other systems use ROUND_HALF_up

[fharper]

after some additional research I wanted to provide the following to clarify and correct my prior post

 I put everything in double and tried a series of calculations. 

 

So [DailyRate] shows the rate as 2.175 and  [DailyRate 1] and [DailyRate 4] both appear to be following the Round-Half-Down rule.  But [DailyRate 3] where I round to 3 dec and then round that to 2 dec behaves like Round-Half-Up or Round-Half-Even. Why would it behave differently for [DailyRate 3]?

So I started to suspect the preview value showing as I already proved it differed from actual value within the tool at times.  I changed the data types to Fixed and walked the decimals from 12 to 15 to 16 and when I reached 16 decimal precision I saw everything change in the preview. 

I totally missed someone's prior post mentioning forcing to high number decimal to see real value.

So the Round within Round method seems to provide the most accurate alignment with our billing system and I have a solution I can use…until I find a discrepancy…

To further complicate the issue I have found that behavior within a Formula tool vs downstream is different. 

Different values in Tool vs downstream

The above is a big issue to be aware of because if you do a calculation in the same tool as the formula that built a value you may not be using the value you think you are.  If you use that field downstream it will may be different.

[fharper]

Just to point out on the last post the value within the tool of [A2a] is 0.035 but the value in the outbound data stream is 0.04.  the data type was cut-off in the illustration but was Vstring to try and show the unvarnished content.  While I have proven in other instance it is clear that a value within the tool can be quite different than the value of the field ounce it goes downstream.  Half a cent variance in a rate will destroy your ability to reconcile.

My solution at this point is two fold...

1. to force to 17 decimal and round to 7 and truncate using regex trick then use that value to further round as needed.  a more complex rate calculation but it seems to be working in reconciliation with our back end systems. 
2. to create fields in one tool and use fields in downstream tools, i.e. avoid using a field in a calculation within the same tool it was created in