Thousands Separators and totals

Hi all,

I have some figures that I'm trying to create into dollar amounts with thousands separators. I am using the formula '$' + ToString([field],0,1) and I'm getting the $ but no commas.

Second issue is that before turning them into strings, I added all the values as Int64 to create one total which was accurate but when I run the workflow after using ToString on the other fields, the total field becomes a long string of each value instead of giving me the correct total whether I use the ToString formula on the total field or not.

I'm not sure what i'm doing wrong, please help!

Common Use Cases

Accepted answers

Gina2021

Hey @jkm1683 !

This was happening to me too today. Not sure if it'll help your workflow, but here was the answer given to me today by @T_Willins (in a multi-field formula tool).

IF IsNull([_CurrentField_]) OR [_CurrentField_] = "[Null]"
THEN ""
ELSEIF Contains(ToString([_CurrentField_]),"%")
THEN [_CurrentField_]
ELSE "$"+ToString(ToNumber([_CurrentField_]),0,1) ENDIF

You may only need this part in a regular formula tool: "$"+ToString(ToNumber([_CurrentField_]),0,1)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Make-a-string-type-appear-as-a-monetary-number-without-changing/m-p/749784#M179824

For the second part of your question, idk if ppl are going to be able to picture your situation. Can you upload your workflow or use the Windows snipping tool to paste a pic?

Gina

Dynamomo

@jkm1683 - you are concatenating the data instead of adding it. This is because the fields are strings and cannot be added...they are concatenated instead. if you want to calculate a total, i would recommend doing it before the fields are turned into Strings.

All comments

Emil_Kos

Hi @jkm1683 , basicly strig arent good for calculation like this.

you should convert the data to string at the end of the workflow and keep it double for the calculation itself.

Gina2021

Hey @jkm1683 !

This was happening to me too today. Not sure if it'll help your workflow, but here was the answer given to me today by @T_Willins (in a multi-field formula tool).

IF IsNull([_CurrentField_]) OR [_CurrentField_] = "[Null]"
THEN ""
ELSEIF Contains(ToString([_CurrentField_]),"%")
THEN [_CurrentField_]
ELSE "$"+ToString(ToNumber([_CurrentField_]),0,1) ENDIF

You may only need this part in a regular formula tool: "$"+ToString(ToNumber([_CurrentField_]),0,1)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Make-a-string-type-appear-as-a-monetary-number-without-changing/m-p/749784#M179824

For the second part of your question, idk if ppl are going to be able to picture your situation. Can you upload your workflow or use the Windows snipping tool to paste a pic?

Gina

Dynamomo

If your end result is an output going to Excel, you may be better off just adding a Table tool while the data is still in numeric format, get rid of the formula appending the dollar sign and it will maintain the value as a number and add a comma when output to Excel. When you add the Table tool, simply add the $ as a prefix to the data and Render out to Excel.

jkm1683

Thank you! I got the commas in finally! Here is a picture of the other issue i was referring to.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703