Domo IDEAs Conference - Beast Modes - Number Formatting

GrantSmith

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.

GrantSmith

Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6

GrantSmith

Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6

Nikhil_Dass

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.

DHo

Is there any hope of Domo implementing the SQL FORMAT() AS function?

This kind of workaround should not be necessary.

marcel_luthi

Thanks, extremely useful for when you want to display multiple summary numbers!

Marc Aec

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.

Jones01

@Marc Aec, @DHo to my surprise this does work even though the validator says non existent function.

concat('£', format(sum(somenumbercolumn), 2))

credit goes to Sam Chalvet for making me aware of this.

Marc Aec

If it works even though it fails validation, then it is at best unsupported and could stop working in the future. If it fails validation, I can't confidently include it in the best mode.