Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Everything You Need to Know About Currency

Ozzie
Alteryx
Alteryx
Created

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 toStrings

 

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
marlline
8 - Asteroid

@Ozzie

 

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

Ozzie
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.

marlline
8 - 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!

GarethLewisWAO
5 - Atom

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

lilianam
6 - Meteoroid

Hello,

When you convert number to a string to get $ in front of it, in the summarize tool i cannot sum the values because they are no longer values, they are strings. 

clipboard_image_0.png

I multiplied my qty*Price (both fixed decimals) to get my Sales col. But I would like my sales to have a currency format, and also be able to sum them up on the summarize tool.

Is there a work around for this?

 

 

Thank you, 

Lilly M. 

Ozzie
Alteryx
Alteryx

Hello Lilly,

 

Unfortunately, you can't do that right now. You currently have to do all of your summarizations first before converting the fields to string in order to display the monetary format. Something that would be cool in the future is to be able to show monetary format on a numeric field if a user configures it. I would highly suggest posting the idea up to our Designer Ideas page. https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas.

 

-Ozzie

serodrig4
5 - Atom

Ozzie - do you know if your macro is still working with xe? I'm trying to run it but keep getting 0 for the converted amounts

thedr9wningman
8 - Asteroid

@Ozzie I'm using PostGres Greenplum and I'm trying to write to a money(255) data type. I'm getting all sorts of conversion errors when attempting to do this and am wondering if my convert-from-a-string-and-then-round-to-two-decimal-places-and-consider-it-a-double isn't good enough?