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.

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.