8 Replies Latest reply on Aug 16, 2018 8:49 PM by Derek Taylor

# Displaying commas in numbers which are shown as string

My data has a field named “Product Target” which is zero wherever the target for a product is not available. To display the zeroes as “Not available”, I created a calculated field as shown below:

IF sum([Product Target1])>0 THEN STR(INT(sum([Product Target1])))

ELSE "Not Available"

END

But as the above calculated field returns a string, the numbers appear like 12345 without any comma separators. Is there any way to insert comma separators in these text fields?

• ###### 2. Re: Displaying commas in numbers which are shown as string

Hi Kanika,

A quick way to solve this would be to find out the length of the string whenever it’s not zero and insert a zero at every third position from the right.  You need to separate every three characters from the end and concatenate them back with a comma between them.

To simplify things, we can assume that the maximum number of digits in the required field is less than or equal to 6. So the calculated field would be something like this:

if sum([product target1])>0 then (if len(str(int(sum([product target1]))))=6 then

left(str(int(sum([product target1]))),3)+","+right(str(int(sum([product target1]))),3)

elseif len(str(int(sum([product target1]))))=5 then

left(str(int(sum([product target1]))),2)+","+right(str(int(sum([product target1]))),3)

elseif len(str(int(sum([product target1]))))=4 then

left(str(int(sum([product target1]))),1)+","+right(str(int(sum([product target1]))),3)

else str(int(sum([product target1])))

end)

else "not available"

end

Hope this works!

2 of 2 people found this helpful
• ###### 3. Re: Displaying commas in numbers which are shown as string

Hey Sreekanth,

This will only work if my field is in Number Format but since its a string format the Thousand separator wont work.

• ###### 4. Re: Displaying commas in numbers which are shown as string

I agree with Teg, you can find more information here:

Number Formatting with Comma Separator inside Parameter Calculation

• ###### 5. Re: Displaying commas in numbers which are shown as string

IF LEN(str([Measure Name]))<4

THEN str([Measure Name])

ELSE LEFT(str([Measure Name]),LEN(str([Measure Name]))-3)+","+MID(str([Measure Name]),LEN(str([Measure Name]))-2,LEN(str([Measure Name]))-(LEN(str([Measure Name]))-3))

END

• ###### 6. Re: Displaying commas in numbers which are shown as string

Much more simplified:

IF LEN(str([Measure Name]))<4

THEN str([Measure Name])

ELSE LEFT(str([Measure Name]),LEN(str([Measure Name]))-3)+","+MID(str([Measure Name]),LEN(str([Measure Name]))-2,LEN(str([Measure Name]))-(LEN(str([Measure Name]))-3))

END

• ###### 7. Re: Displaying commas in numbers which are shown as string

This one should work until 100,000,000,000 :

if len(STR(ROUND([Product Target],0)))>3 then

left(STR(ROUND([Product Target],0)),(len(STR(ROUND([Product Target],0)))-1)%3+1)+","

+if len(STR(ROUND([Product Target],0)))>9 then mid(STR(ROUND([Product Target],0)),len(STR(ROUND([Product Target],0)))-8,3)+"," else "" end

+if len(STR(ROUND([Product Target],0)))>6 then mid(STR(ROUND([Product Target],0)),len(STR(ROUND([Product Target],0)))-5,3)+"," else "" end

+right(STR(ROUND([Product Target],0)),3)

else STR(ROUND([Product Target],0)) END

1 of 1 people found this helpful
• ###### 8. Re: Displaying commas in numbers which are shown as string

Struggled with this and could not get any provided solutions to work. I wanted a solution with no limit to length of source number. Then I found this on StackExchange and it worked well:

REGEXP_REPLACE(STR(SUM([YourField])),"(\d)(?=(\d{3})+\$)","\$0,")

2 of 2 people found this helpful