3 Replies Latest reply on Jun 27, 2016 10:22 AM by Dmitry Chirkov

# How to convert string to number using tableau calulation

Hi All,

I have created a bar chart and I need to show overall CRF Pages in the label. I created the calculated field as  "Overall CRF Pages - " + STR(SUM([Page Count])) to show the heading as shown in the below screen shot.

However I need the resulting value  after the overall sales text with a thousand separator ',' as shown in the below screen shot.

I created the calculated field as:

IF LEN([Overall PageCount]) > 3 THEN

"Overall CRF Pages - " + LEFT([Overall PageCount],2) + "," + RIGHT([Overall PageCount],3) + " Pages"

END

This works fine for the 5 values, however, its not working with more than 5 values.

Here i attached the sample packaged workbook for your reference.

• ###### 1. Re: How to convert string to number using tableau calulation

Have you tried the below?

IF sum([Sales]) >= 1000 and sum([Sales])  < 10000 THEN
"Overall Sales - " + LEFT(str(sum([Sales])),1) + "," + mid(str(sum([Sales])),2,3)

elseif
sum([Sales]) >= 10000 and sum([Sales]) < 100000 THEN
"Overall Sales - " + LEFT(str(sum([Sales])),2) + "," + mid(str(sum([Sales])),3,3)

elseif
sum([Sales]) >= 100000 and sum([Sales])  < 1000000 THEN
"Overall Sales - " + LEFT(str(sum([Sales])),3) + "," + mid(str(sum([Sales])),4,3)

elseif
sum([Sales]) >= 1000000 and sum([Sales])  < 10000000 THEN
"Overall Sales - " + LEFT(str(sum([Sales])),1) + "," + mid(str(sum([Sales])),2,3) + "," + mid(str(sum([Sales])),5,3)

END

• ###### 2. Re: How to convert string to number using tableau calulation

Hi Kannan--

Number Formatting with Comma Separator inside Parameter Calculation

Matt Lutton wrote this beauty and he took hammered it out the good-old fashioned way: analyzed each sum and applied the correct formatting. Hopefully your data won't exceed 100 Trillion.

His code, which I'm sure you can tweak, is:

----------------------

PARAMETER CALC

CASE[Choose Measure]

when "Sales" then

IF len(str([Sum Sales]))<3 THEN

"\$" + str([Sum Sales])

ELSEIF len(str([Sum Sales]))<=6 THEN

"\$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-3)

+ "," + RIGHT(str([Sum Sales]), 3)

ELSEIF len(str([Sum Sales]))<=9 THEN

"\$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-6)

+ "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + ","

+ RIGHT(str([Sum Sales]), 3)

ELSEIF len(str([Sum Sales]))<=12 THEN

"\$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-9)

+ "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

+ "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + ","

+ RIGHT(str([Sum Sales]), 3)

ELSEIF len(str([Sum Sales]))<=15 THEN

"\$" + LEFT(str([Sum Sales]), Len(str([Sum Sales]))-12)

+ "," + MID(RIGHT(str([Sum Sales]), 12), 1, 3)

+ "," + MID(RIGHT(str([Sum Sales]), 9), 1, 3)

+ "," + MID(RIGHT(str([Sum Sales]), 6), 1, 3) + ","

+ RIGHT(str([Sum Sales]), 3)

ELSE

"More than 100 Trillon Dollars"

END

when "Ratio" then str(round(sum(Profit)/([Sum Sales])*100, 2))+"%"

end

------------------

Good luck!

• ###### 3. Re: How to convert string to number using tableau calulation

You just need to format the label itself.

1. Put measure you want on the label
2. Format it the way you need (separators, numbers after comma etc)
3. Modify label's text - your 'Overall ..." text goes there.

1 of 1 people found this helpful