13 Replies Latest reply on May 23, 2017 8:24 AM by Okechukwu Ossai

# How to Convert Number to Str as a dimension

Hello Friends,

I am trying to convert a number to a string with two decimals and get the result as a dimension, but am not able to get the desired output and could anyone please help how to do that? The reason I am specifically looking to get this as a dimension because I want to use this field to establish relationship with a different data source. Attached is the dashboard to show what I am trying to do.

Sales - Number value

Str - Converted to str and getting this calc field dimension but not able to restrict it to 2 decimals

str1 - Able to restrict it to 2 decimals but not able to get this field under dimension.

Need your guidance and support to fix this friends

• ###### 1. Re: How to Convert Number to Str as a dimension

Hey Ram ,

Guess , I do have Sanjivini for you

You can do with Str only , just follow few steps.

STEP 1 STEP 2

Then Use Number(Custom) as 2 Decimal Places It will remain as Dimension what you want (Hope Laxman (Tableau Dashboard ) will be fine)

Thanks,

Ritesh

• ###### 2. Re: How to Convert Number to Str as a dimension

If you do not want to worry about number formating, you can do:

STR(ROUND(number,[decimals]))

i.e. STR(ROUND([Sales]),2)) //Round sales to nearest two decimal and then convert to a string.

E.g. • ###### 3. Re: How to Convert Number to Str as a dimension

Hi,

if i understand your problem correctly, you are looking to round value of Sales to 2 decimal places but with datatype as String?

Try this calculation in your STR calculation:

STR(INT([Sales]))

+'.'

+STR(

IF INT(LEFT(SPLIT(STR([Sales]),'.',2),3))-INT(LEFT(SPLIT(STR([Sales]),'.',2),2)+'0') >= 5

THEN

INT(LEFT(SPLIT(STR([Sales]),'.',2),2))+1

ELSE

INT(LEFT(SPLIT(STR([Sales]),'.',2),2))

END

)

1 of 1 people found this helpful
• ###### 4. Re: How to Convert Number to Str as a dimension

Hi Ram,

If your requirement is to get it as a dimension with 2 decimals

I would suggest creating a calculated field as below

ROUND([Sales],2)

This will be a dimension and i think you should be set.

Let me know if you have any question. Mark this helpful if it was

• ###### 5. Re: How to Convert Number to Str as a dimension

Hi Sid. That has been proposed already above

• ###### 6. Re: How to Convert Number to Str as a dimension

Haha just realised. I don't know why it wasn't showing the replies when i was looking at the thread.

May be i am a slow typer

• ###### 7. Re: How to Convert Number to Str as a dimension

Hello Ritesh ~ Thanks for your guidance, but I see that the number field is grayed out? Any idea? • ###### 8. Re: How to Convert Number to Str as a dimension

Thanks Daniel,

I already tried this. If you see I have Str1 that has the similar formula, and I get the required result but it is in measures and not getting converted to dimension so that I can use this as join with a different data source. Any suggestions?

Ram

• ###### 9. Re: How to Convert Number to Str as a dimension

Thanks Sachdeva,

looks like its working, but some values as NULL (last line) and any suggestions how to solve this • ###### 10. Re: How to Convert Number to Str as a dimension

Hi Ram,

You can convert [Sales] to string as a Dimension with 2 decimal places using the steps below.

1. Create calculated field [Str v1]

STR(ROUND([Sales], 2))

2. Finally, create calculated field [Str]

IF LEN(RIGHT([Str v1], LEN([Str v1])-FIND([Str v1],"."))) = 2 THEN [Str v1]

ELSEIF LEN(LEFT(RIGHT([Str v1], LEN([Str v1])-FIND([Str v1],".")),3)) = 1 THEN [Str v1]+"0"

ELSEIF FIND([Str v1],".") = 0 THEN [Str v1]+".00"

ELSE

IF INT(LEFT(RIGHT([Str v1], LEN([Str v1])-FIND([Str v1],".")),3)) % 10 < 5

THEN LEFT([Str v1], LEN(LEFT([Str v1],FIND([Str v1],".")))+2)

ELSE LEFT([Str v1], FIND([Str v1],"."))+STR(INT(MID([Str v1], FIND([Str v1],".")+1, 2))+1)

END

END

That's it!

Hope this helps.

Ossai

2 of 2 people found this helpful
• ###### 11. Re: How to Convert Number to Str as a dimension

Thanks Sachdeva, Please ignore my earlier update. I got the issue and managed to fix it

Regards,

Ram

1 of 1 people found this helpful
• ###### 12. Re: How to Convert Number to Str as a dimension

Thanks Ossai,

This solution works perfectly

Regards,

Ram

1 of 1 people found this helpful
• ###### 13. Re: How to Convert Number to Str as a dimension

You are welcome Ram.

Could you mark it as helpful, so it can be useful to others who may have this same question in the future?

Ossai