New Feature - Normal Rounding tool, function or Datatype

[Seth]

Be able to have a round function similar to Excel's Round Function. Basically, be able to round "5 and up" or "4 and down" based on the number of decimal spaces we choose. This would be similar to what is taught in grade school and would resolve 80% to 90% of the rounding issues in Alteryx. Most of rounding solutions provided are too technical and worthless. This is very basic and common sense. (FYI - I don't know if this helps, but I believe Excel only goes out to 13 decimal places before cutting it off, maybe if Alteryx could do the same with the rounding it would be helpful. 

Currently I'm pulling the exact same data through two different workflows, and getting two different amounts slightly off from each even though both use the same raw data source and datatype.  

[joshuaburkhow]

Just to be sure, this doesn't do it for you right? https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Round-Numeric-Values-with-th...

[Seth]

That is correct. For me, where this issue becomes critical is in the area of validations and using the Alteryx Test tool. Currently, I'm in the process of migrating several Excel spreadsheets with financial data to a database. I'm using Alteryx to transform and structure them so that they can be uploaded.

When building the workflows I'm creating a separate validation workflow that compares the data in the golden copy (Excel) to the database to make sure they equal each other. I will then use the Alteryx Test tool to make sure they equal each other and give me an error if they don't.

Because, Alteryx and Excel rounds things differently the Test tool keeps kicking me an error saying they don't equal, when in fact they do out to the second decimal place. Currently, I've bypassed this issue by using an Alteryx Floor function to just chop off the decimals. In my world if things are off a few pennies it is considered immaterial. But, in digging into the issue I wasn't alone with this problem and all of the solutions posted failed. I was very frustrated because I wasted an inordinate amount of time on something that I believe should be very basic. I recognize that maybe a few people in the non-financial world need to go out to the 50th decimal place, but it would be nice if both needs could be met with two separate functions. 

FYI - I believe Excel now goes out to 15 decimal places before chopping/truncating it, but I could be wrong. It has been awhile since I've looked into the issue.

[ABakos]

Hi Seth,

What if you used Round with ABS and If?

if [Nr] < 0 then Round(ABS([Nr]), 0.01) * -1 else Round([Nr], 0.01) endif

[Shihan]

I highly support this to be implemented. I was gonna post an Idea, but seen Seth's post here and commenting on it.

There are number of posts and Ideas like Seth did on this issue. But other than workaround, there is no straight forward tool/formula developed yet which could solve this matter.

We know the technical reason for this behavior and we know some workaround (not 100% accurate though) as well. But that is "workaround", not a one-step solution. The post from KevinP describes the reason/behavior for it and the best workaround (to my opinion, from number of workarounds including one I have created) is from danilang in a post (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-Round-4-975-0-01-getting-4-97-...) where he mentioned a simple formula [round([Number]+0.00000001,0.01)].

The reason I am emphasizing this point like Seth is, the world is still on Excel, and this is not going to change in the near future. People are and would be using excel for basic to advance calculation on Excel all over the world. And with the flexibility of input/output and the power that Alteryx has, the output data would not just be used for internal use, that would be used for the external customers as well (external customer of Alteryx users). If the data volume is bigger, the sum of difference would make a big difference if the customers are using Excel.

One of the considerable example I can give is calculating TAX for Invoice consolidations. With the Alteryx default calculation it makes a huge difference, and with the workaround method it still off few cents to couple dollars depending on the amount and data volume. This difference on this kind of calculations would never be acceptable to the customers. People/organizations will always do auditing with their own tools and they will not consider the computer’s fascinating behavior on floating point.

We (Alteryx users) are not separated from rest of the world, we will need to exchange data. And as an enthusiast Alteryx user, we want the outputs become acceptable in broader scale. Having so many powerful tools in Alteryx, adding one more small tool or enhancement to solve this issue would make life so compatible with others.