How do I put a comma in multiple summary numbers?
How do I fix my beastmode to add commas to my dollar amounts?
CONCAT('Total Revenue - $',ROUND(SUM(`Revenue`)),'<br>Total Net Profit - $',ROUND(SUM(`Net Profit`)))
Result:
Total Revenue - $54783
Total Profit - $12455
But what I want to see is:
Total Revenue - $54,783
Total Profit - $12,455
Thank you,
Nicole
Comments
-
You might check out this thread. It's a few years old but I think it still applies today.
I don't think there's an easy solution right now. Because there isn't we typically just round our numbers to the nearest thousand or million, depending on the size of the business and the audience.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Give this a try. You'll have to replace the inners with the fields from your database. I didn't build the code with optimization in mind, so you're welcome to tweak it as needed.
CONCAT(
CASE WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 9
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 12
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 7, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 10, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 11
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 6, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 9, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 10
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 5, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 8, 3)
)
ELSE 'UNKNOWN'
END)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 6
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 9
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 7, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 8
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 6, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 7
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 5, 3)
)
ELSE 'UNKNOWN'
END)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 3
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 6
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 5
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 4
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
)
ELSE 'UNKNOWN'
END)
ELSE RIGHT(CONCAT('',ROUND(Sum(`SALES`), 0)),3)
END
,' || ',
CASE WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 9
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 12
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 7, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 10, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 11
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 6, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 9, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 10
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 5, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 8, 3)
)
ELSE 'UNKNOWN'
END)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 6
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 9
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 7, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 8
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 6, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 7
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 5, 3)
)
ELSE 'UNKNOWN'
END)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 3
THEN
CONCAT('$',
CASE
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 6
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 5
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
)
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 4
THEN
CONCAT(
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
,',',
SUBSTRING(
CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
)
ELSE 'UNKNOWN'
END)
ELSE RIGHT(CONCAT('',ROUND(Sum(`PROFIT`), 0)),3)
END
)1 -
Marc_H, doesn't seem like it should be this challenging to do something as simple & routine as formatting a number string, but dang - you sure saved me with this! Our DOMO users are going to appreciate you proving a 'template' for this. Thank you sir!
0 -
Hi @Marc_H, i tried using the following code. I get error again and again whenever i am using Round and Sum functions. Without these i get the correct output but we need an aggregated sales number as the output for which i need to use Sum and Round functions.
can you help me out here.
Thank you
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive