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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dollar sign on all number fields.

Plewis1
8 - Asteroid

Good afternoon alteryx team,

 

I built a formula to add dollar signs to all the fields that needed it. It worked but then I realized it added to all fields with numbers. I just need it only on the currency columns. The formula I built looks like this "$"+ToString([Field Name],0,1). Also I wanted the numbers to be rounded and shortened. As of now some numbers look like this $6.3013698630137. How do I get it shortened too $6.30? Thank you in advance. 

8 REPLIES 8
BrandonB
Alteryx
Alteryx

You can uncheck the fields that you don't want the formula to be applied to in the configuration window of the multi-field formula. It may be good to use another multi field formula before this one that converts everything to a fixed decimal format with size 8.2. The 2 after the decimal specifies that it will be rounded to the 100's place. 

Plewis1
8 - Asteroid

Okay i only picked the fields I wanted in the Multi field formula. Why I didn't notice that before is beyond me. Okay so I set another multi field formula tool before the one I already had. What type of formula do I use to get the data to a fixed decimal format with size 8.2? Thank you BrandonB. 

Plewis1
8 - Asteroid

NVM I figured out what you are saying. Thank you Brandon. 

Plewis1
8 - Asteroid

Wait it says an expression must be specified? 

BrandonB
Alteryx
Alteryx
You can just set current field as your expression. Technically it is just using the same values, but it does need to use that. So that way the formula is using the same value that currently exists.
Plewis1
8 - Asteroid

Yes sir. That worked. Thanks again Brandon. 

CWatson
5 - Atom

I attempted to use the formula you recommended to convert a number to $ format but I receive an error that says I need to change it from "ToString" to "ToNumber".  But neither work for me.  It tells me a get a Mismatch in operator +.  Whatever that means.

BrandonB
Alteryx
Alteryx

@CWatson you will need to create a new column with a string data type because a dollar sign is a text value. You can think of the plus sign working with two strings as a concatenate. 

 

"String"+" Second String"

 

results in

 

"String Second String"

 

So when you are looking to create a formula that combines two string fields. If one of the fields you are adding together (concatenating) is not a string, you will either need to use a select tool beforehand to convert it, or do the conversion in your formula. You are getting the mismatch in operator because it is trying to add a string and a number together. You will want to make sure that both are strings. 

Labels