# Custom Summary Number using Beast Mode with Thousands Separators

Options
Member

Hello, I'm interested in using thousands separators in a custom summary number I built using a Beast Mode. The number to be separated is a summation using a case statement. For example, the number is 122435453.88 and I want the thousands separators. Thanks!

• Contributor
Options

Hi,

I think the following beast mode will do the trick:

CASE WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 9 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),3,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),6,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 8 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,2),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),3,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),6,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 7 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,1),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),2,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),5,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 6 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),4,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 5 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,2),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),3,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 4 THEN
CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
),1,1),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),2,3))
WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) <= 3 THEN
CONCAT(Round(ABS(Your_Field_Name),0),'')
END

Tell me how it goes.

Hope this helps.

MajorDomo@Lusiaves

**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.
• Member
Options

Hi there,

wow. this is a lot of beast mode for what I hope will be a short function call someday

I did something similar - and used a part of your code - THANKS - because I want abbreviations only past >9999:

`(CASE WHEN SUM(YOUR_FIELD) > 999999 THEN CONCAT (ROUND(SUM(YOUR_FIELD)/1000000), 'M')WHEN SUM(YOUR_FIELD) > 9999 THEN CONCAT (ROUND(SUM(YOUR_FIELD)/1000), 'K')WHEN SUM(YOUR_FIELD) > 999 THEN CONCAT(SUBSTRING(CONCAT(Round((SUM(YOUR_FIELD)),0),''),1,1),',',SUBSTRING(CONCAT(Round((SUM(YOUR_FIELD)),0),'' ),2,3))ELSE SUM(YOUR_FIELD)END)`

Perhaps it helps somebody else...

Thanks !