on 03-09-2016 02:26 PM - edited on 07-27-2021 11:43 PM by APIUserOpsDM
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!
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.
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!
I used this formula, but the commas and the dollar sign is being added, but the commas are not.
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.
Try increasing the size of the string. The default string length isn't always large enough to capture the entire string.
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