Custom Summary Number using Beast Mode with Thousands Separators
Comments
-
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),'')
ENDTell me how it goes.
Hope this helps.
Ricardo Granada
**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.1 -
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 !
0
Categories
- 10.6K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 63 Kowaza
- 296 仲間に相談
- 649 ひらめき共有