Macro for rounding (Hint: Alteryx Round() does NOT do this correctly)

[jdminton]

I ran into this issue when a co-worker was having trouble with the Round() expression in the formula tool.

After some research on the community, I found the reason, which seems to be how Alteryx stores the values in a binary format. There was a pretty complicated explanation of why that you can see here if you really want to know.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Alteryx-Odd-Behavior/td-p/8908...

On this other post, https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Rounding-Discrepancies/td-p/50... there was a possible solution that worked most of the time. It's an ingenious way to resolve the issue, but I was confused as to why this remains an issue for Alteryx. 

If you are working with data where the rounding in the last decimal place is not important, you can likely just rely on the Alteryx round formula. I have worked with many CFOs in the past, however, and know that any detectable "error" will bring into question everything else in your analysis. Due to this, I developed a macro, which is really just a formula tool, but I set it up as a macro to be able to share with others who may not want to change the formula tool to meet their needs every time. 

The included macro has a browse tool in it as well to display how it is working and comparing to the results provided by Alteryx's Round() expression. The build design is simple (which rounding should be), but instead of using the number as a number, I break it down into its components.

First, I set the number of decimals to round to. Next I convert the number into text. This way, I can pull the specific digit that the rounding is based upon. Using this digit, I can manually test if it is 5 or more (round up) or less than 5 (round down). This is what you would have to do if manually rounding each number by hand.

After that, I am pulling the part of the number that precedes the rounded digit. This was done by using the Floor() expression of the original amount times 10 to the power of the number of rounding places. So 2 decimal rounding would be times 100, 3 times 1000, and so on. Finally, I add a 1 (round up) or 0 (round down) based on the rounding test times 10 to the power of the number of rounding places. In the data included in the macro, you can see this would add .001 for the amounts where the rounding would round up.

I'm curious if anyone who has experienced this rounding issue in the past could run the macro against the data to see the results. I am pretty confident though that this method will provide 100% accuracy on rounding where other methods seem to fail.

Enjoy! 😀

[ArnaldoSandoval]

Hi @jdminton 

I downloaded your workflow but it does not load properly, check the screenshot below:

Then looking at the workflow's XML you are referencing the macro from a local folder of your environment, I suggest you package your demo to work properly so it wont deter other for trying it.

BTW: I like the macro,

Best regards,

Arnaldo

[jdminton]

Thanks @ArnaldoSandoval . I had forgotten it would unlink the macro from the original workflow. I added the packaged workflow now. The macro itself was what I wanted to share though. The workflow was included to show how the macro would appear in the workflow. Thanks for the assist.

[ArnaldoSandoval]

Hi @jdminton 

It worked from the package 👍 but I don't like its icon (just kidding)

Arnaldo

[jdminton]

@ArnaldoSandoval 😂

[caltang]

Thanks for raising this @jdminton ! I always brute force with CEIL / FLOOR and it's quite lengthy. ROUND() doesn't always cater to what I need as well.

[jdminton]

I never knew this existed as an issue until my co-worker noticed it! I wonder how many other methods have been used to get around this in the past?

[jdminton]

I realized when I went back to my 2022.3 version of Alteryx, that the macro might provide a message to users not on the latest Alteryx version. I don't think there's any impact to the execution, but here is a 2022.3 version of the macro for those not upgraded yet.

[mkhtran]

The edge case (where this macro does rounding "wrong") is when the underlying value is actually correct.

Often the underlying value has a higher precision than the intended calculation, so the display value (which is rounded to a degree) ends up being the "correct" value that you want to round based off (which this macro does). E.g., I want to round 0.1 + 0.2 which the computer calculates to be 0.30000000000000004. This is displayed as "0.3" which turns out the be the correct value.

But the other situation is that the underlying value is accurate. That I truly want to round the value 1.024999999999999911182158029987476766109466552734375 which ends up being displayed as "1.025". In this case (when rounding to 2 decimal places), Alteryx's Round() is correct, whereas this macro rounds an already rounded value, and ends up wrong.

[jdminton]

@mkhtran Great catch. I'm wondering now if the values provided that "don't round correctly" have this issue. Do you know of any way to display the data in full or at least more than the handful of digits showing in the record? Also, I'm curious why Alteryx does not identify that value as having been truncated as it sometimes does.

Although going back to it again, I typed the numbers in so they definitely don't have the issue of being a longer number truncated for display. So this does truly sound like an edge case. I'm going to explore fixing for this in the macro. Thanks!