8 Replies Latest reply on Nov 30, 2017 10:03 AM by Marc-Olivier Legris

    Changing Number Format in a String

    Dhaval Thakkar

      Hi,

       

      I have created a calculated field which is a string:

      attr ([Name]) + ", " +  str(str(Abs(sum([Value]))) + ", " + str(round(((sum([Value]) / TOTAL(sum([Value])))*100),0))+"%")

      This is used to display Labels on a pie chart.

       

      An example out is:

      Subscriber, 1234567.0, 39%

       

      Instead, I want the output to appear as:

      Subscriber, 1,234,567, 39% (See the number format).

       

      I have tried rounding off to zero decimal places, but that does not help. Also I am not able to put commas for thousand separators.

      Please help.

        • 1. Re: Changing Number Format in a String
          Shawn Wallwork

          Hi Dhaval, this works in the Super Store data:

           

          STR(ROUND(LOOKUP(sum([Sales]),0),0)) + ", " +

          str(round(((sum([Sales]) / TOTAL(sum([Sales])))*100),0))+"%"

           

          And produces this:

           

          Dhaval.PNG

           

          Not sure why the ROUND() didn't work for you. Give it another try. The LOOKUP() is a Joe Mako trick to add the thousands separators in.

           

          --Shawn

          • 2. Re: Changing Number Format in a String
            Dhaval Thakkar

            Thank you so much Shawn. This works.

             

            Dhaval

            • 3. Re: Changing Number Format in a String
              Bill Sherby

              Does this approach still work for everyone or was this "bug" fixed in more recent versions (8 and 8.1)?  I have tried this approach as such: "Total Achieved Benefit : $" + STR(ROUND(LOOKUP([Total Benefit (copy)],0),2))

               

              This does not fix the formatting to provide comma separation, which I would prefer.  Any update on this issue would be great.

               

              Thanks!

              Bill

              • 4. Re: Changing Number Format in a String
                Matt Lutton

                It was a bug that was fixed.  I'm certain there are other ways to get the comma in there, but cannot recall off the top of my head how to do it.   

                 

                Found that old thread: http://community.tableau.com/thread/134849

                 

                Hope that helps you.

                • 5. Re: Changing Number Format in a String
                  William Robson

                  Got the same problem 3 years later.

                  if anyone else is running into this my solution is as follows ([Selected Metric] being an int, STR(ROUND([Selected Metric],0) being the string i'm adding commas to:

                   

                      MID(

                          REGEXP_REPLACE(

                              IF LEN(STR(ROUND([Selected Metric],0)))%3 = 0 THEN ''//If number contains 3,6 e.tc. digits do not append anything

                              ELSE STR((3-LEN(STR(ROUND([Selected Metric],0)))%3)*5) //Otherwise apeend a 5(one digit) or 10(2 digits) to make n digits a multiple of 3

                              END +

                              STR(ROUND([Selected Metric],0)),

                              '(\d{3})','$1,') //regex adds a comma after any sequence of 3 digits

                          ,IF LEN(STR(ROUND([Selected Metric],0)))%3 = 1 THEN 3 ELSE LEN(STR(ROUND([Selected Metric],0)))%3 END //start at second or third digit

                          ,LEN(STR(ROUND([Selected Metric],0)))+(LEN(STR(ROUND([Selected Metric],0)))/3)) //stop after lengh reached plus number of commas

                   

                  basically this works in 3 steps:

                  1) Append either a 5, 10 or nothing to the start of the numberto ensure the number of digits is a multiple of 3. this is required as the regex used next will append a comma AFTER every 3 digits (a reverse function would have been handy here):

                              IF LEN(STR(ROUND([Selected Metric],0)))%3 = 0 THEN ''//If number contains 3,6 e.tc. digits do not append anything

                              ELSE STR((3-LEN(STR(ROUND([Selected Metric],0)))%3)*5) //Otherwise apeend a 5(one digit) or 10(2 digits) to make n digits a multiple of 3

                              END +

                              STR(ROUND([Selected Metric],0))

                  2) Use Regular expression to add a comma after every 3 digits:

                  REGEXP_REPLACE(

                              'String created above with n digits appended',

                              '(\d{3})','$1,') //regex adds a comma after any sequence of 3 digits

                  3) use MID to remove added leading digits and trailing comma

                      MID(

                         'String Created Above with Commas Added'

                          ,IF LEN(STR(ROUND([Selected Metric],0)))%3 = 1 THEN 3 ELSE LEN(STR(ROUND([Selected Metric],0)))%3 END //start at second or third digit

                          ,LEN(STR(ROUND([Selected Metric],0)))+(LEN(STR(ROUND([Selected Metric],0)))/3)) //stop after lengh reached plus number of commas

                  2 of 2 people found this helpful
                  • 6. Re: Changing Number Format in a String
                    Melissa Nichols

                    This was very helpful...........for positive numbers. However, when dealing with negative numbers, the negative sign is counted as a leading digit in a string, so the commas are getting misplaced in these instances.

                     

                    I am using a hack to remove the leading negative sign before adding in the commas, but if there is a more efficient way, I'd love to hear about it!

                     

                    Can anyone PLEASE help me with this issue? I'm pretty sure it's in the following line of code, but I can't be 100% sure. Any and ALL additional insight and expertise is greatly appreciated!!!!

                     

                            ,IF LEN(STR([(Parameter) Summary Measure - Difference (#)]))%3 = 1 THEN 3 ELSE LEN(STR([(Parameter) Summary Measure - Difference (#)]))%3 END //start at second or third digit

                    • 7. Re: Changing Number Format in a String
                      Brian Biggs

                      I'm having good luck using Pooja Gandhi's regular expression on this post:

                      Need REGEXP_REPLACE to add thounsands seperators

                       

                      Note: it works with negative numbers but not for non-integers.

                      • 8. Re: Changing Number Format in a String
                        Marc-Olivier Legris

                        Hi Melissa Nichols

                        Inside your code use ABS([YourParameter]) to remove the minus sign.

                        Then prefix the whole operation with the following expression:

                         

                         

                        IFNULL(regexp_extract(str(sign([YourParameter])),'(-)'),'')  + ( Your_original_code_goes_here )