11 Replies Latest reply on Sep 24, 2018 4:50 PM by Shinichiro Murakami

# Formatting a number/percentage as a string

I have this expression:  str(round(sum([YTD Full Amount]),0)).

Currently the value of 3,500,343 shows as 3500343, for example.

I tried str(round(lookup(sum(YTD Full Amount),0),0)) to no avail. Any help would be appreciated!

• ###### 1. Re: Formatting a number/percentage as a string

Nate,

What's wrong with the first formula?

• ###### 2. Re: Formatting a number/percentage as a string

The value shows as a string (as it should because of the STR), but I need it to be formatted like a number (thousands separators, etc.) but still be a string. For instance, it shows as 4392034 (for example). I need 4,392,034.

• ###### 3. Re: Formatting a number/percentage as a string

Oh understand. Its tricky I'll let you know if I come up with something. But can I ask why you want it as String instead of Number?

• ###### 4. Re: Formatting a number/percentage as a string

From the source system there is no \$, % symbols. So I need to append those to the numbers.

• ###### 5. Re: Formatting a number/percentage as a string

For that, you could leave it as integer and then right click on the dimension - > Default Properties -> Number format and change it to dollar or % symbols, right?

• ###### 6. Re: Formatting a number/percentage as a string

There could be different data types per another description field. So it needs to be description by description based on another field, not one format for the entire column, if that makes sense.

• ###### 7. Re: Formatting a number/percentage as a string

Nate, I have a very small not so good solution. Assuming your number count won't go over 100 Million, it can be manually formatted as follows. If it goes beyond 100 Million, you could add another elseif condition and format accordingly.

Having said that, There might be better solution other experts might know. You can use this as your last option.

IF LEN(STR([Type]))<= 3 then STR([Type])

ELSEIF LEN(STR([Type]))=4 then LEFT(STR([Type]),1)+','+RIGHT(STR([Type]),LEN(STR([Type]))-1)

ELSEIF LEN(STR([Type]))=5 then LEFT(STR([Type]),2)+','+RIGHT(STR([Type]),LEN(STR([Type]))-2)

ELSEIF LEN(STR([Type]))=6 then LEFT(STR([Type]),3)+','+RIGHT(STR([Type]),LEN(STR([Type]))-3)

ELSEIF LEN(STR([Type]))=7 then LEFT(STR([Type]),1)+','+MID(STR([Type]),2,3)+','+RIGHT((STR([Type])),3)

ELSEIF LEN(STR([Type]))=8 then LEFT(STR([Type]),2)+','+MID(STR([Type]),3,3)+','+RIGHT((STR([Type])),3)

ELSEIF LEN(STR([Type]))=9 then LEFT(STR([Type]),3)+','+MID(STR([Type]),4,3)+','+RIGHT((STR([Type])),3)

END

• ###### 8. Re: Formatting a number/percentage as a string

The problem is that it's already a part of something like this:

IF min([Financial Metric]) = 'some text value' THEN '\$'+str(round(sum([YTD Full Amount]),0))

ELSEIF min([Financial Metric]) = 'some text value' THEN '\$'+str(round(sum([YTD Full Amount]),0))

ELSEIF min([Financial Metric]) = 'some text value' THEN '\$'+str(round(sum([YTD Full Amount]),0))

...and there's about 30 more lines of code after that. I'm afraid adding your code would be too unwieldy.

• ###### 9. Re: Formatting a number/percentage as a string

Bump - any other thoughts?

• ###### 10. Re: Formatting a number/percentage as a string

Don't know this is the best way, but probable works.

Thanks,

Shin

• ###### 11. Re: Formatting a number/percentage as a string

Following up.