comma and decimals formatting issue

[jboschee]

Hi everyone,

I'm having some issues formatting numbers.  Here is what I've tried so far.

regex_replace([GROSS_SALES],"\d{1,3}(?=(\d{3})+(?!\d))","$&,")

This seems to work for gross sales, but when I do the same formula for net sales I get something like this:

0.0,000,000,000

'$'+TOSTRING([GROSS SALES]/[Budget Rate],2,1)

This worked for my conversion to USD as long as the number of local currency is set as a fixed decimal.  If the local currency is string, it doesn't work.

If I try the 2nd formula for the gross and net sales in local currency, the comma's are missing.

Any help is greatly appreciated.

Thanks in advance!

[MarqueeCrew]

@jboschee,

check out this tostring() help:

https://help.alteryx.com/2018.1/index.htm#Reference/Functions.htm?Highlight=Tostring

 ToString([Gross_Sales],2,1,".")

cheers,

mark

[jboschee]

Didn't work unfortunately.  Comma is still missing from gross sales.

[MarqueeCrew]

Please show a sample input and output record

[jboschee]

Here are the sample input and output records.  

[MarqueeCrew · Accepted solution]

@jboschee,

I don't know why your input data is being read as a string, but if you configure a multi field formula and select all types of data (check the desired fields) then use this formula it will work:

ToString(ToNumber([_CurrentField_]),2,1,".")

Cheers,

Mark

[jboschee]

This worked!  Thanks!

[tnrdhd]

Thanks, this worked for me. I just had to increase the field size.

[LeroyMagee]

Hi. If you're in the Multi-field formula, where do you insert that formula AND how do you incorporate several different fields into the formula?

[MK_Alteryx411]

I have been searching a while for this format fix. This thread resolved it. Thank you very much @MarqueeCrew!