10 Replies Latest reply on Dec 5, 2018 11:35 AM by Kaitlin DiPaola

    String with number formatted as currency

    Rossella Blatt Vital

      Hi,

       

      I need to create a string containing a number formatted as currency, for example:

       

      "This is my string containing my formatted number: $ 651,226.00"

       

      The number is a calculation and is formatted as desired (currency, 2 decimal places, prefix $ etc.). Though when I concatenate the strings the formatting disappears and the string will become:

       

      "This is my string containing my formatted number: 65122600"

       

      The only solution I was able to come up with so far is to create two different fields: one field contains only the string and another field containing only the number (which can be formatted as desired). Then both fields are dropped into the text mark of the view. I needed to create a field also for the text only because the content of it depends on a parameter.

       

      This works fine when there are only a few strings and numbers, but it would be very time consuming for a string containing several numbers.

       

      Do you have any idea how this can be achieved in a more efficient way?

      Thanks for any advice

      Rossella

        • 1. Re: String with number formatted as currency
          Shawn Wallwork

          Rossella, as long as you are NOT using an extract then the RAWSQL_STR function is probably available to you. The calculation would probably be something like this:

           

          "This is my string " + RAWSQL_STR("FORMAT(%1,'Currency')",[My_Number])

           

          Here are some other handy formats:

           

          RAWSQL_STR("FORMAT(%1,'$###,###')",[My_Currency_No_Cents])

          RAWSQL_STR("FORMAT(%1,'Percent')",[My_Percent])

          RAWSQL_STR("FORMAT(%1,'##.0')",[My_One_Decimal_Always])

           

          Note: '#' shows a number if there is one, whereas '0' always show a number and a zero if there is no number.

           

          I use these alot when I'm using a parameter to drive a case statement which includes various number formats. Here's a partial example:

           

          RAW.png

           

           

          --Shawn

          • 2. Re: String with number formatted as currency
            Rossella Blatt Vital

            Hi Shawn,

             

            thanks for your help.

            Unfortunately I am using an extract.

             

            Any idea how to do it in that case?

            Thanks

            Rossella

            • 3. Re: String with number formatted as currency
              Shawn Wallwork

              Well sort of, but it ain't pretty and it will probably be pretty slow. You'll need to parse your number to build it as a string. So if your number was 70000 and you wanted 70,000 then you'd write this:

               

              LEFT(STR([My_Number]), 2) + ',' + RIGHT(STR([My_Number]), 3)

               

              But then you're going to need to add a bunch of logic using LEN() to determine how long the number is and then build the string accordingly. You'll also need to search for any decimals FIND([My_Number], '.') and then subtract this from LEN() to get the proper number of decimal places. It all very tedious and can get complicated very quickly.

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: String with number formatted as currency
                Rossella Blatt Vital

                Hi Shawn,

                 

                thanks! That's exactly what I was looking for!

                 

                Thanks a again a lot.

                Rossella

                • 5. Re: String with number formatted as currency
                  Daniel Mark

                  Excellent tip Shawn! This helped me as well.

                   

                  I even took your advice a step further (as you suggested) and applied the logic to parse for string values ranging from 1-12 in length:

                   

                  Character Count:

                   

                  LEN(STR(ROUND(SUM([METRIC]),0)))

                   

                  String Formula:

                   

                  IF [CHARACTER COUNT] = 1 THEN "Of " + "$" + STR(ROUND(SUM([METRIC]),0)) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 2 THEN "Of " + "$" + STR(ROUND(SUM([METRIC]),0)) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 3 THEN "Of " + "$" + STR(ROUND(SUM([METRIC]),0)) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 4 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),1) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 5 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),2) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 6 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 7 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),1) + "," + MID(STR(ROUND(SUM([METRIC]),0)),2,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 8 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),2) + "," + MID(STR(ROUND(SUM([METRIC]),0)),3,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 9 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),3) + "," + MID(STR(ROUND(SUM([METRIC]),0)),4,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 10 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),1) + "," + MID(STR(ROUND(SUM([METRIC]),0)),2,3) + "," + MID(STR(ROUND(SUM([METRIC]),0)),5,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 11 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),2) + "," + MID(STR(ROUND(SUM([METRIC]),0)),3,3) + "," + MID(STR(ROUND(SUM([METRIC]),0)),6,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  ELSEIF [CHARACTER COUNT] = 12 THEN "Of " + "$" + LEFT(STR(ROUND(SUM([METRIC]),0)),3) + "," + MID(STR(ROUND(SUM([METRIC]),0)),4,3) + "," + MID(STR(ROUND(SUM([METRIC]),0)),7,3) + "," + RIGHT(STR(ROUND(SUM([METRIC]),0)),3) + " Budget"

                  END

                   

                  -Dan

                  4 of 4 people found this helpful
                  • 6. Re: String with number formatted as currency
                    Kim McAvoy

                    I cannot believe it has to be this hard to format a number into a string!!!  Surely there is something Tableau can do to help here!?

                    • 7. Re: String with number formatted as currency
                      Shawn Wallwork

                      Kim there is an easier way to do this. Post a new question with a sample packaged workbook and I'll take a look. Please include the version of Tableau you are working with, and ping me using Shawn Wallwork. Thanks,

                       

                      --Shawn

                      • 8. Re: String with number formatted as currency
                        Paula Wigger

                        Hello Shawn,

                         

                        You mention an easier way to do this and I am running into the same problem. I have slick way to get the decimal place issue (5 into 5.00 or 4.5 into 4.50).

                         

                        '$' +

                        left([NumberString]+

                            if find([NumberString],'.') = 0

                            then '.00'

                            else '00'

                            end

                            , find([NumberString]+'.','.')+2

                            )

                         

                        I am just trying to find a better way to add the commas. Do you have any ideas?

                        • 9. Re: String with number formatted as currency
                          Mark Palmberg

                          Did Kim McAvoy ever post that workbook, Shawn Wallwork? I'm plugging away at a version of Daniel's effort above but keep muttering to myself about the complexity of it all. I know, I know, it's a drawing application!  I've included a small pkgd workbook here but am glad to submit a new issue if you prefer. Thank you.

                          • 10. Re: String with number formatted as currency
                            Kaitlin DiPaola

                            Shawn Wallwork

                            I'm having a similar issue, I think, in that I want to show two different formats for one measure. I have attached a sample workbook so you can see what I'm working with. I would like all impact types except employment to be in dollars, and employment to be in units. I was able to create another dimension to get employment to show in units, but it also still shows dollars. Any help would be greatly appreciated! Thanks!