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:

--Shawn

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

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

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!?

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

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?

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.

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!

I can't believe Tableau doesn't have this baked in but... here is another solution for dollars, no cents under a billion.

First create a no decimal string as a placeholders to simplify programming:

[Total Billed as Str No Decimals]

'$' +

STR(ROUND(SUM([Total Billed]),0))

Then use the string to know where to place the commas:

IF ISNULL([Total Billed as Str No Decimals]) THEN

   'N/A'

ELSEIF LEN([Total Billed as Str No Decimals]) <= 4 THEN

      [Total Billed as Str No Decimals]

ELSEIF LEN([Total Billed as Str No Decimals]) <= 7 THEN

      LEFT([Total Billed as Str No Decimals], LEN([Total Billed as Str No Decimals]) - 3) +

      ',' + RIGHT([Total Billed as Str No Decimals], 3)

ELSE

      LEFT([Total Billed as Str No Decimals], LEN([Total Billed as Str No Decimals]) - 6) +

      ',' +

      LEFT(RIGHT([Total Billed as Str No Decimals], 6), 3) +

      ',' + RIGHT([Total Billed as Str No Decimals], 3)

END