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.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 211 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 18 Automate
- 355 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive