Domo Idea Exchange - Beast Modes - Abbreviated Summary Number

GrantSmith
GrantSmith Indiana 🔵

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.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!