Domo Idea Exchange - Beast Modes - Abbreviated Summary Number
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to format numbers a number in an abbreviated format.
Problem:
When concatenating a string with a number the resulting value is a string and unable to utilize Domo's built in formatting.
Solution:
This version only needs to check for length in increments of 3 to determine the correct units (Billions, Millions etc). The case statement goes in a descending order because the case statement will enter the condition that evaluates to true first. So every condition below the top one implies that the first condition is false.
-- Author: -- Created: -- Last Modified: -- Description: Since the numbers are being concatenated with a string -- Default Domo number formatting is no longer possible. This will format the -- total of the `random_number` field based on how large the number is. -- ROUND will format the abbreviation to 2 decimal places. CONCAT( 'Total: ', -- CASE statements will evaluate the first expression it finds to be true and then exists. -- No further conditions are evaluated -- If >= 10 digits then it's in the billions -- It would need to be expanded to 13 for (T)rillions CASE WHEN LENGTH(ROUND(SUM(`random_number`), 0)) >= 10 THEN CONCAT(ROUND(SUM(`random_number`) / 1000000000, 2), 'B') -- 7 digits means Millions WHEN LENGTH(ROUND(SUM(`random_number`), 0)) >= 7 THEN CONCAT(ROUND(SUM(`random_number`) / 1000000, 2), 'M') -- 4-6 digits = thousands WHEN LENGTH(ROUND(SUM(`random_number`), 0)) >= 4 THEN CONCAT(ROUND(SUM(`random_number`) / 1000, 2), 'K') WHEN LENGTH(ROUND(SUM(`random_number`), 0)) <= 3 THEN ROUND(SUM(`random_number`), 0) WHEN SUM(`random_number`) IS NULL THEN 0 ELSE '' 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
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive