Formatting number field (Decimal and Commas)

[hii_amit]

Hi,

how to hide the decimals and adding commas without compromising the decimal in overall total

Datatype used is Double

Input

1220.6

Output

1221

In output decimal of .6 should not lose its value while calculating the numbers as it might give big variance when we sum huge data.

Thanks 

[atcodedog05]

Hi @hii_amit 

Why dont you have 2 columns one in Int (rounded off) one in double (decimals)

Use Int for display. Use double for calculation filter it out later.

Or do the calculation before when field is double after calculation change it to Int(Round)

Just a thought😅 Hope this helps🙂

[hii_amit]

data is quite huge, hence cant create additional columns, moreover this is required in as excel output, hence don't think it will work. as we don't want to see decimals in excel but don't want to loose the value as well.

[j_acon]

Did you try using the reporting tools? 

Pass the data to create a table using the Table tool, inside here, make sure the double fields you have are set to display 0 decimals.

Then render the table with the Render tool

In the output you will see whole numbers, but when you click on the cell you should see the decimals.

[hii_amit]

I am generating 8 to 10 sheets in 1 single excel file using output tool

if i will use the render tool i think i cant create that much sheets as column count in some sheets is close to 400, reporting tool wont support that

[j_acon]

If you want to use VBS script, you can write it to convert the format of the columns to Number, that will keep the decimal value (195.1) , but it will display (195) 

So you can generate the output and on the events tab, you ask alteryx to use Cscript to run a vbs that will change the format of columns your files.

[thuyduongnguyen]

Hi @j_acon May I ask if we could use Table tool to show the percentage as output, e.g 12% instead of 0,12? If I choose Overwritten excel file with preserve formatting option, it works. But I want to ask specific if the Table tool could do it or not.

[L_T]

You might use the toString([Field1], 0, ",", "."), then Select Tool to convert the column to "double"

[j_acon]

@thuyduongnguyen You can add Suffix on the column configuration in of the Table tool. 

[apathetichell]

@j_aconis totally correct! use the suffix section of column configuration and add a "%"

one quick thing - if you need to multiply it beforehand you can use a formula to do round([percentage,.0001)*100 to get a more standard percentage look and feel. General consensus is that you cannot use the formula block in column rows to do this - so do it in a previous formula so .12 is 12% not .12%.