Rounding to the Nearest Dollar Using #Alteryx

Introduction

What I describe in this article is a small piece of a fairly big and complicated Alteryx workflow that involves a lot of operations. I wish I could explain the whole sequence of events, but for now, I have to settle on this small technical detail related to rounding currencies to the nearest dollar.

I recently had to do some work to join two separate databases that had a field in common (well, almost). One database had actual airline ticket prices such as (567.34), ticket refunds (-823.4) and other airline fees (28). The currency format ranged from 2 to 1 to 0 decimal digits, as just shown. The other database had these same types of currencies, but they were rounded to the nearest dollar in Excel. This meant they had no decimal digits. To be able to match records across the databases, I had to perform a rounding operation on the actual ticket currencies shown as examples above (as well as operate on some other fields). I had over 100,000 records in each database that I was trying to match.

Rounding Methods

Before you assume that this is going to be a simple thing to do by using a round() function, please be patient. I’m going to tell you what I did and why I had to do it. You might think I’m crazy, but I’ll show you some proof that you have to pay attention to details to perform data operations like this.

Theory

Remember back in 5th or 6th grade when you first learned how to round numbers? You probably learned how to round-up and round-down. Well, if you remember those rules, you can apply them to perform the currency rounding operation I discuss in this article.

For positive currencies, if the number of cents is 50 or higher, the number of dollars gets increased by 1. This is a round-up formulation for the midpoint of 50 cents. For example, a ticket price of 356.50 gets rounded to 357.  A ticket value of 356.49 gets rounded to 356.

For negative currencies, if the number of cents is 50 or higher, the number of dollars gets decreased by 1. For example, a refunded ticket of -345.50 gets rounded to -346. If this confuses you at first, think of this. Presume the ticket price is -345.51, rather than -345.50. Clearly, -345.51 is closer to -346 than -345. This is still a round-up formulation, but because you are in negative territory, the number of negative dollars gets larger. That is why the number of dollars gets decreased by 1 for negative currencies but gets increased by one for positive currencies. This form of rounding is symmetrical about zero and this is the method that is used in Excel.

 

Rounding Methods

Here are the Alteryx rounding methods I used:

1. Round – a formula of round([ERMS Ticket Price (USD)],1) is used
2. Smartround – a formula of smartround([ERMS Ticket Price (USD)]) is used
3. Manual rounding to the dollar – formulation shown in next section

Here are the Excel methods included:

1. Round – an Excel formula of =ROUND(D2,0) is used
2. Roundm – a formula of =MROUND(D2,1) is used

I passed 8,461 currencies through these five methods and compared the results. Table 1 shows that the only two rounding methods that matched every case were the Alteryx method 3 (Column E, my method) and the Excel method number 1 (Column I). Note that the round functions of Alteryx (#1 above) and Excel (#1 above) have different terms passed as the second parameter (1 for Alteryx, and 0 for Excel). That is because these functions are not designed the same and are not supposed to be perfect matches to one another. It just turns out that they are exactly the same for most conditions – except one.

Figure 1 – Table of rounding results using five different methods for rounding currencies to the nearest dollar. The only two methods that matched all records were columns E and I.

---

 

The differences detected between the round functions of Alteryx and Excel is limited to one specific condition. The Alteryx round function (column G) did not match the Excel results (Column I) for the 17 cases (out of a test case of 8,461 records) where negative currencies had exactly 50 cents for the ticket price. As shown in the first 17 rows of Figure 1 (rows 2 -> 18), the Alteryx round function (Column G) as currently programmed rounds negative currencies that have 50 cents the other direction (like a round down, rather than a round up). So for me to match the rounded and un-rounded ticket prices, I had to write my own custom Alteryx workflow for currency rounding to the nearest dollar.

The Manual Rounding to the Dollar Method

Figure 2 shows the four-step workflow I developed for rounding to the dollar. Four steps were needed because of the incoming data formats I had to handle. I had to make sure that a number like 123.8 was interpreted correctly as 123.80 rather than 123.08.

Figure 2 – The rounding to the dollar workflow.

---

 

Figures 3 through 6 show the four steps needed to transform the incoming currency field to a field that is rounded to the nearest dollar. Step 1 is shown in Figure 3, in which the incoming currency is split into dollars and cents using the “.” as the delimiter.

Figure 3 – Split the incoming currency into dollars and cents.

---

 

Step 2 is shown in Figure 4, in which the dollars and cents fields are renamed after the parsing operation.

Figure 4 – Rename the dollars and cents fields.

---

 

Step 3 is shown in Figure 5, in which the cents are converted to a two-digit number.

Figure 5 – Properly convert the cents field into two digits from 00 to 99. This is needed to avoid a number like 123.8 being intepreted as 123.08.

---

 

Step 4 is shown in Figure 6, in which the logic for rounding to the nearest dollar is implemented. The logic handles ticket prices that include 50 cents like Excel does, both in the positive and negative currency conditions.

Figure 6 – Perform the rounding to the nearest dollar operation.

---

 

Click here to download an Excel file that contains the test data and the content of the Alteryx *.yxmd file. To use this file, you just have to copy the XML code off of the second worksheet and save it in a text file.

Final Thoughts

One of the best features of Alteryx is that you can design your own routines for processing data the way you need to. In this case, even though the Alteryx round() function didn’t give me exactly what I needed, I was able to write my own custom function in the form of a quick, 4-step workflow. I designed and built the logic to do what I needed, when I needed it.

As a worker in the field of modern-day analytics, I have to be flexible in how I handle data. Alteryx gives me that flexibility. All the controls necessary for me to write a custom rounding function were readily available to me. I love that about Alteryx.

While doing this work, I came across a nice table that shows various Excel rounding operations in Figure 7.  Click here to go to the original source.

Figure 7 – Various rounding operations performed in Excel.

---