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

Thousands Separators and totals

jkm1683
5 - Atom

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!

12 REPLIES 12
Emil_Kos
17 - Castor
17 - Castor

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
8 - Asteroid

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-monetar... 

 

 

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
11 - Bolide

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
5 - Atom

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

 

image.png

Dynamomo
11 - Bolide

@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.

jkm1683
5 - Atom

AhHa, genius!  I thought I was adding them as numbers but i was adding them as strings. Thank you so much, I have my total now!

Dynamomo
11 - Bolide

@jkm1683 

I really think that you should look at considering the Table tool for what you are trying to do though.  The Table tool allows you to take numbers and output them with commas and $ signs, or even % signs if it is a percent field.

Attached is a small example.

Eli_B
10 - Fireball

Hi!

 

Since your fields are strings, the formula tool is actually doing exactly what you are asking it, which is to append each column together. To do anything mathematical, the field type needs to be an Integer, Double, Float, or Fixed Decimal. The Table tool trick above is a quick way to keep your data as is while adding in prefixes & commas, also giving the ability to add some column/row formatting.

jkm1683
5 - Atom

Thank you, that came out looking really nice!

Labels