How to format a number to add in Currency, 2 decimal places and a comma

[jdelaguila]

Good Morning Alteryx Community!

I have a workflow that I'm trying to build that i have not gotten to work yet. 

In a nutshell i have a field called [MRC] that has numbers in it. 

1st step - Round the numbers to the nearest dollar.  Ex. 4.22 would go to 4. 

2nd step - Create 4 new fields Multiply the new rounded number by 1.5, 2, 2.5 and 3. The catch here is the new number also needs to be rounded to the nearest dollar. 

3rd step - Convert the number to include currency symbol, 2 decimals and a comma. So if the final number is 1050 it would convert it to $1,050.00. 

---------------------------------------------------

So as a visual - this is what i would like to end up with. 

This is what we start with	Rounds to the nearest dollar	MRC_ROUNDED*1.5 & Needs to be rounded to the nearest dollar, and $, decimals and commas	MRC_ROUNDED*2 & Needs to be rounded to the nearest dollar, and $, decimals and commas	MRC_ROUNDED*2.5 & Needs to be rounded to the nearest dollar, and $, decimals and commas	MRC_ROUNDED*3 & Needs to be rounded to the nearest dollar, and $, decimals and commas
MRC	MRC_ROUNDED	A1	A2	A3	A4
250	250	$375.00	$500.00	$625.00	$750.00
15.25	15	$23.00	$30.00	$38.00	$45.00
25	25	$38.00	$50.00	$63.00	$75.00
1400.3	1400	$2,100.00	$2,800.00	$3,500.00	$4,200.00
3200	3200	$4,800.00	$6,400.00	$8,000.00	$9,600.00
100.5	101	$152.00	$202.00	$253.00	$303.00
456012	456012	$684,018.00	$912,024.00	$1,140,030.00	$1,368,036.00
160.33	160	$240.00	$320.00	$400.00	$480.00
8	8	$12.00	$16.00	$20.00	$24.00
4.22	4	$6.00	$8.00	$10.00	$12.00

I've attached my current workflow. Obviously the attached workflow is not working but any ideas would be greatly appreciated. Also if I'm doing this the long way - any thoughts on what would tool would be faster?

Thanks

Javier

[PhilipMannering · Accepted solution]

Hey @jdelaguila 

I think this does what you need.

[AngelosPachis · Accepted solution]

Hi @jdelaguila ,

You have to use two key functions here. The one is a round function, that will allow you to round to the nearest dollar, and the other is a tostring function.

The round function should be 

Round([Field you want to round],1)

and the to string function will allow you to define the number of decimals (2) and whether you need a thousand separator (1 stands for true, so you need a thousand separator)

hence the expression should be 

Tostring(Field],2,1)

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

[Jon-B · Accepted solution]

Hi Javier

You're so close! I think the solution for step 2 is to move the *1.5, *2... inside the rounding function. See attached workflow.

As regards a different way of doing it, what you're doing is fine, but a multi-field formula tool removes the need to type out multiple formulae in the same tool, that are essentially the same, so just reduces typing errors really.

Hope that helps 😃

[jdelaguila]

Philip, Angelos, Jon,

Thank you so much for your quick response and suggestions. Each one of you were able to answer my question and also showing me there is more than 1 way of doing something in Alteryx. All the suggestions worked perfectly! Thank you.

Javier

[tmocrz001]

what does the number mean on round?


Ex: 
Tostring(Field],2,1)

[nbrenner]

These examples didn't start with a number in the thousands that didn't have a comma separator initially and successfully add one at the end.  I am applying these functions and it is not adding in a thousands comma separator.

Thoughts?