Domo IDEAs Conference  Beast Modes  Number Formatting
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to format numbers a number when concatenating with a string.
Problem:
When concatenating a string with a number the resulting value is a string and unable to utilize Domo's built in formatting.
Solution:
Check the length of the number using the LENGTH
function and then get the appropriate segments of the number using SUBSTRING
and then combining them all together using CONCAT
 Author:  Created:  Last Modified:  Description:  Checks the length of a number to determine how to insert thousands separators (,) using SUBSTRING to get 3 digit pieces of the number. CONCAT('Total Amount: ', CASE WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 12 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 10, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 11 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 9, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 10 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 9 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 8 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 7 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 6 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 5 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 4 THEN CONCAT( SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',', SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3) ) WHEN LENGTH(ROUND(SUM(`random_number`), 0)) <= 3 THEN ROUND(SUM(`random_number`), 0) WHEN SUM(`random_number`) IS NULL THEN 0 ELSE 'ERROR' END )
Note:
These beast modes only go to 100 Billion (12 digits). Any number higher would need to expand using the same logic for higher lengths.
**Did this solve your problem? Accept it as a solution!**
Comments

Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Hey Grant, this was super helpful. We were able to add commas as per the INR formatting as well. event the total is working in this case.
0 
Is there any hope of Domo implementing the SQL
FORMAT() AS
function?This kind of workaround should not be necessary.
2 
Thanks, extremely useful for when you want to display multiple summary numbers!
0 
I was surprised when I tried to use FORMAT in a beast mode and got an error "This calculation is using a nonexistent function". Then I was shocked to find threads going back to 2018 asking for a way to format numbers when converting them to text. I believe that FORMAT is even available in the old version of MySQL that Domo is using. It's very disheartening that this has been left unaddressed for at least 5 years.
1
Categories
 All Categories
 1.3K Product Ideas
 1.3K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 281 Workbench
 2 Cloud Amplifier
 4 Federated
 2.8K Transform
 83 SQL DataFlows
 541 Datasets
 2.2K Magic ETL
 3.2K Visualize
 2.3K Charting
 516 Beast Mode
 26 Variables
 555 Automate
 129 Apps
 408 APIs & Domo Developer
 18 Workflows
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 342 Distribute
 84 Domo Everywhere
 257 Scheduled Reports
 1 Software Integrations
 81 Manage
 80 Governance & Security
 2 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 77 Community Announcements
 4.8K Archive