community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Everything You Need to Know About Currency

Alteryx
Alteryx
Created on

Topics discussed below:

Currency Field Formatting

Currency Conversion

 

Currency Field Formatting - Strings to Doubles

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.

 

stringtodouble.jpg

 

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.

 

formulacurr.jpg

 

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.

 

multiformulacurr.jpg

 

 

Currency Field Formatting - Doubles to Strings

 

stringtodouble.jpg

 

After you have done your calculations and you want this back into currency format you can simply use this expression in your formula tool:


'$'+ToString([FieldName], 2, 1)

 

More info about this formula here.

 

Now on to a fun topic: Currency Conversion

 

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.

 

currdropconfig.jpg

 

The base URL we make for this request is http://www.xe.com/currencyconverter/convert/?Amount=1&From=”FROM”&To=”TO”

 

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.

Attachments
Comments
Asteroid

@Ozzie

 

So if I want $99.66 in numeric format, is it possible?

Alteryx
Alteryx

@marlline

 

Unfortunately no it would not because the "$" is a string character. You must take the dollar sign out when converting to a numeric format.

Asteroid

@Ozzie

 

Alright. Thank you!

 

Also can you show me how to @ me? When I @ you, there is no hyperlink showing up. I am newbie here. Thank you!

Hi,

 

Using some of the above advice, I've tried to change currency e.g. £300,000.50  (I'm from the UK!), from text to a numeric using ...

 

snip for alterux.PNG 

 

 but I'm left with 300.0.

 

So, although I'm losing the '£', which is good, I'm losing any numbers after comma's. and also limiting results to 1 decimal place (I need 2).

 

Also, for any negative values, I'm losing the minus sign '-'.

 

Any suggestions?

 

Sorry for the long winded explanation, I'm new to this.

Thanks,

Gareth