Forum Discussion

ArunkumarK's avatar
ArunkumarK
Icon for Helper I rankHelper I
7 years ago
Solved

Thousand Separtors

Hello Everyone, I needs to convert the number into US thousand separtors like below. How to conver the number into highlighed formats like below.   Available Number: 125000 Available Format: 1,25...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Arun,

     

    For me it defaults to the US version when I select the field and select comma in modeling. If your default is diferent, you could perhaps force it using Format command such as 

    newdata = FORMAT(data[Column1],"#,0.")
     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Arun,

when I used the value function to cast the text as a number, it did keep the commas but added decimal places.  

newdata = value(FORMAT(data[Column1],"#,0."))  
 
I also changed the original column to a text field and the 'newdata' still came out formatted correctly ( but with decimals).

 

ArunkumarK's avatar
ArunkumarK
Icon for Helper I rankHelper I
7 years ago

I assume my words make you confused. Even I get the answer same what you got but when you sum the column values it wont come because of it converted into text.

 

E.g If a column have multiple rows with data and we formated in such a way then in the report section if you try to sum, it wont allow except count.

 

Hope make sense.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Yes, you are right, I am not quite following.  I am getting commas correctly when displaying the numbers individually or as a sum (see image).  I added a few more rows and then showing a sum in a cardfile.  Sorry, I am not sure how to help you further since I am getting the correct data (as I understand it). 

     

    • ArunkumarK's avatar
      ArunkumarK
      Icon for Helper I rankHelper I
      7 years ago

      Thanks for your understanding and response.

      But still I am sure the table shown below is just displaying the data but if you try to display the total it wont come if you formatted using Format Funct.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I understand the 2nd issue now. Using the value function causes the Sigma sign next to the column to disappear, even though it is technically a number.  As I understand it, only columns with that sign will show totals within a table or matrix.  You can still show sums at a row level within the table or in cards, but it will not show total within the table. You may want to pose that as another question in the community and see if anyone else has ideas on showing totals in a table when using Value.