1 2 Previous Next 18 Replies Latest reply on Jun 6, 2018 11:24 PM by chitra keluskar Branched to a new discussion.

    Custom Number Format

    Anuja S Krishnan

      I want to use a customer number format to display currencies as per the Indian number system.

       

      I am attaching a .twbx here where in Sheet1 numbers are displayed in this format --> ###,###,###

       

      I need to display the numbers in this format instead --> ##,##,###

       

      So, the table should look like this below:

       

      Test
      A88,009
      B8,73,645
      C12,34,897
      D1,62,94,304

       

      Any idea how this can be done? I tried to use Excel code in the custom format but it did not work for me!

        • 1. Re: Custom Number Format
          Jonathan Drummey

          Hi Anuja,

           

          I don't know how to do this using Tableau's custom number formatting, maybe someone else will or you can reach out to Tableau tech support. Have you tried setting your workbook locale and/or Windows Region & Location settings?

           

          Depending on your data source, you could get this using string formatting via a calculated field that uses RAWSQL or your own formula. So, for example, you could build a view using the measures, then add in the calculated field to use as the label & tooltip. However, this has a couple of issues that don't have good workarounds:

           

          - The axis legends will show a different number format. Theoretically you can do some tricks in a dashboard to overlay axis legends with a separate worksheet with the proper values, and it's painful to get the formatting right.

          - You can't build text tables using Measure Names/Values, since you're not allowed to use text values for Measure Values. So you're forced to use all discrete pills on Rows to build the tables, which can make for problems with formatting, limits you to 16 columns, and can create additional complexities when the view has a finer grain of detail than is displayed (i.e. you end up with unwanted Null value rows).

           

          Jonathan

          • 2. Re: Custom Number Format
            Sreehari Katageri

            Hi ,

             

            I want the following number formatting in custom number format

             

            if number is 10,000 then it should be 10,000

            if number is 100,000 then it should be 1,00,000

            if number is 1,000,000 then it should be 10,00,000 etc .....

             

            custom format should be dynamic like below excel formula

             

            [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

            • 3. Re: Custom Number Format
              Sreehari Katageri

              I need the comma separated values not just the blanks. In the Kettan response, it has blanks but i need comma's as well.

              • 4. Re: Custom Number Format
                Shawn Wallwork

                You mean this:

                 

                Or this:

                 

                The slash tells Tableau to make it a literal.

                 

                --Shawn

                • 5. Re: Custom Number Format
                  Sreehari Katageri

                  But the above solution is static it works only if the digits in the format 100,000, but it do not work if the digit in the format 100,000,000.

                  For example it works if the value is 209,020 but it do not work for 49050. In this case it shows ,49,050

                  • 6. Re: Custom Number Format
                    Shawn Wallwork

                    First Tableau does not have this functionality, this would be a good one to put in the Idea section.

                     

                    There is a very limited workaround:

                     

                    But it would only account for two different formats, and all values would need to be positive.

                     

                    --Shawn

                    2 of 2 people found this helpful
                    • 7. Re: Custom Number Format
                      Sreehari Katageri

                      Thank you so much. It worked

                      • 8. Re: Custom Number Format
                        Sreehari Katageri

                        Hi ,

                         

                        its working for 1,00,000 & 10,000 & 1,000 . But for 3 digit number it shows like ,100 . It prefix the comma at the beginning . Is there any work around for this?

                        • 9. Re: Custom Number Format
                          Shawn Wallwork

                          Like I said, it is very limited and only works for 2 different formats:

                          No I don't know of any other workaround.

                           

                          --Shawn

                          • 10. Re: Custom Number Format
                            Sreehari Katageri

                            Thank you so much. Its the perfect solution, I was looking for.

                            • 11. Re: Custom Number Format
                              kota spandana

                              Hi Kettan,

                               

                              I want the numbers to be displayed in Indian Number format. I followed your method and got the results for some dashboards. But, the method is not working for others. Please suggest me solution for this:

                              Dashboard.jpg

                               

                              I want the Indian Number system to be applied to Sale Value at last. I have written calculations for number system and placed them in Measure Values Shelf. I am getting each column for each calculation.

                              Dashboard2.jpg

                               

                              This is not the format I expect. So, Please help me with this ASAP.

                               

                               

                              Thanks,

                              Spandana K

                              • 12. Re: Custom Number Format
                                Shawn Wallwork

                                Kota, it ain't pretty. And don't even think about running this against millions of records, but this calc will take you out 11 digits with the proper India formatting:

                                 

                                EDIT: As usual I find my mistakes after posting. So here's the new formula that actually works:

                                 

                                CASE LEN( STR(ROUND( [Sales Test] ,0)) )

                                WHEN 1 THEN STR( ROUND( [Sales Test] ,0) )

                                WHEN 2 THEN STR( ROUND( [Sales Test] ,0) )

                                WHEN 3 THEN STR( ROUND( [Sales Test] ,0) )

                                WHEN 4 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 1 ) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 5 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 2 ) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 6 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 1 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 2, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 7 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 2 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 3, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 8 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 1 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 2, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 4, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 9 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 2 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 2, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 4, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 10 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 1 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 2, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 4, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 6, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                WHEN 11 THEN LEFT(STR( ROUND( [Sales Test] ,0)), 2 ) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 2, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 4, 2) + ','

                                        + MID(STR(ROUND([Sales Test] , 0)), 6, 2) + ','

                                        + RIGHT(STR( ROUND( [Sales Test] ,0)), 3 )

                                END

                                 

                                And each digit does need to be a different formula.

                                Also the new workbook attached.

                                 

                                 

                                Cheers,

                                 

                                --Shawn

                                3 of 3 people found this helpful
                                • 13. Re: Custom Number Format
                                  Simon Runc

                                  hi Kota,

                                   

                                  4 years and counting...but you may as well add you vote to this idea (your vote could be the one that tips the scales!)

                                   

                                  https://community.tableau.com/ideas/1411

                                   

                                  we shouldn't have to go to such lengths (however clever, Shawn!) to be able to dynamically format values. I often have 'measure selectors' where the selected measure can be %, Integers, 2 DPs...and have to create a formula for each one if I want it do display, in a readable-to-end-user format.

                                  1 of 1 people found this helpful
                                  • 14. Re: Custom Number Format
                                    Yuriy Fal

                                    Hi all,

                                     

                                    Shawn, your calc is great.

                                     

                                    Though I couldn't resist

                                    to introduce another (buggy) one.

                                     

                                    Please find the attached.

                                    It is your wb with mods.

                                     

                                    Yours,

                                    Yuri

                                    1 2 Previous Next