Domo IDEAs Conference - Beast Modes - Number Formatting

GrantSmith
GrantSmith Coach
edited March 2023 in Beast Mode

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&amp;index=6

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Here's a link to the video of my session outlining this beast mode: https://www.youtube.com/watch?v=gO8OLpsAk4M&amp;index=6

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Nikhil_Dass
    Nikhil_Dass Domo Employee

    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
    DHo Member

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

    This kind of workaround should not be necessary.

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

  • maec
    maec Member

    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.