Currency formula, or adding the comma separator

[CristonS]

If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!

Often, clients try to format a plain numeric value to a currency format. A simple way to accomplish this is to use the optional "addCommas" parameter in the ToString function. This function converts a numeric value to a string, using a user-specified number of decimal places [numDec]. Though Alteryx doesn’t require the third parameter, if called, the third parameter inserts the appropriate commas (1 or true means format with commas).

ToString([original_value], 2, 1)

Since the new value is a String, simply add in the dollar symbol:

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

Happy Alteryx-ing!

[MassimoDM]

when I try to do this, starting from a type double number, I get the following error: 

The formula "TOTAL" resulted in a string but the field is numeric. Use ToNumber(...) if this is correct.

and if I try to change the field to a string beforehand, the ToString() just doesn't work, input and output remain exactly the same.

[CristonS]

hi @MassimoDM  we can figure this out.

First, are you creating a new field, or operating on an existing field? Creating a new string field will be more straightforward, call it [new_TOTAL] or something relevant. Set it to a string type, then use the formula. (You'll still need the ToString to operate on the double field [TOTAL]).

[new_TOTAL] = '$'+ToString([TOTAL], 2, 1)

Hope this helps! 

[mgroleau]

I used this formula, but the commas and the dollar sign is being added, but the commas are not.

[katiesullivan]

Same here - the formula works on some of my numbers but not on others. Adds the $ to all but the decimals only if it doesn't end in ".00" and no commas for any of them. 

[bb213]

@mgroleau @katiesullivan 

Try increasing the size of the string. The default string length isn't always large enough to capture the entire string.

[bb213]

Also, I prefer this formula because it looks better for negative numbers (I'm using the Multi-Field Formula Tool): 

if [_CurrentField_] <0 then
'-$'+ToString(abs([_CurrentField_]), 2, 1)
else
'$'+ToString([_CurrentField_], 2, 1)
endif