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

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

jdelaguila
8 - Asteroid

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 withRounds to the nearest dollarMRC_ROUNDED*1.5 & Needs to be rounded to the nearest dollar, and $, decimals and commasMRC_ROUNDED*2 & Needs to be rounded to the nearest dollar, and $, decimals and commasMRC_ROUNDED*2.5 & Needs to be rounded to the nearest dollar, and $, decimals and commasMRC_ROUNDED*3 & Needs to be rounded to the nearest dollar, and $, decimals and commas
      
MRCMRC_ROUNDEDA1A2A3A4
250250$375.00$500.00$625.00$750.00
15.2515$23.00$30.00$38.00$45.00
2525$38.00$50.00$63.00$75.00
1400.31400$2,100.00$2,800.00$3,500.00$4,200.00
32003200$4,800.00$6,400.00$8,000.00$9,600.00
100.5101$152.00$202.00$253.00$303.00
456012456012$684,018.00$912,024.00$1,140,030.00$1,368,036.00
160.33160$240.00$320.00$400.00$480.00
88$12.00$16.00$20.00$24.00
4.224$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

6 REPLIES 6
PhilipMannering
16 - Nebula
16 - Nebula

Hey @jdelaguila 

 

I think this does what you need.

 

PhilipMannering_0-1615221912388.png

 

AngelosPachis
16 - Nebula

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)

 

AngelosPachis_1-1615222010530.png

 

hence the expression should be 

 

Tostring(Field],2,1)

 

 

AngelosPachis_0-1615221941720.png

 

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

 

Cheers,

 

Angelos

 

 

Jon-B
7 - Meteor

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 😃

 

Jon-B_0-1615222302474.png

 

 

 

 

 

jdelaguila
8 - Asteroid

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
7 - Meteor

what does the number mean on round?


Ex: 
Tostring(Field],2,1)

 

nbrenner
7 - Meteor

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?

Labels