This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We often get a lot of questions from new users about how to convert fields with currency formats to doubles and vice versa. If you have currency fields in your data that come in to Alteryx with the currency format (ex: $1,354.00) and you want to perform any kind of numeric calculations, you must convert these fields to numeric fields (i.e.: Double, FixedDecimal, Int 32 etc…). For more information on data types click here.
There are a couple ways to convert this string format to a numeric format. Below I will demonstrate two ways: one is using the formula tool, the other is using the multi-field formula tool. Both are similar but one is a bit more flexible than the other.
Using the Formula Tool:
If you’re using the formula tool you must make a new field for the converted string. Make sure the type is numeric, I usually go with Double. Then use the formula shown below. The first part of the expression will replace the $ and comma with empty strings. While ToNumber() will change it to a number.
Using Multi-Field Formula:
Using the multi-field formula is very similar to the formula tool. In this tool we can specify multiple fields if we so choose. Also we don’t have to make a new field. We can actually change the ones we already have. In the configuration, make sure you have “Text” fields pop up in the selection window and select the fields you want to change. The formula in this case is exactly the same except you use [_CurrentField_] as your field variable. This variable will run through the expression for all fields that you selected at the top.
Currency Field Formatting - Doubles to Strings
After you have done your calculations and you want this back into currency format you can simply use this expression in your formula tool:
For those who gather data that comes in as a different currency than what they want in their reports could always look at conversion rates manually and do the math themselves (using Alteryx of course). The only problem with that is it becomes tedious and currency conversion rates change all the time. The best way to get this real time data is to do an API call to a website which offers this data in real time.
The workflow I have attached has a macro that I have built (version 10.6), which allows a user to choose the currency their field is in with a dropdown interface and convert it to a different currency. This macro uses the xe.com free API to get currency conversion rates in real time.
The “FROM” and “TO” will change when the user chooses the currencies. After that happens, these will get replaced with the currency’s ISO 4217 code and the download tool will gather all information pertaining to that URL. After some parsing, we obtain the currency conversion rate and place it into its own field, from which we use to calculate our new currency.
To learn about APIs and how to connect to them using Alteryx. I would check out this article.