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.
**Was this post helpful? Click Agree or Like below**
**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
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 ひらめき共有