Custom Summary Number Alerts

Laf8991 · November 2023

I'm currently using a custom summary number that has been configured using the CONCAT function on a standard table which points out the overall spend for a specific client for the previous day and then says what the total spend for that client currently is.

I want to set up an alert which will just share the contents of the summary number every morning so that we can more easily keep track of spend.

It won't allow me to create an alert and gives me a message saying that there are no alertable metrics found on the card.

Is there any way for me to setup the table to make an alert for my custom summary number?

jessdoe · November 2023

Grant has several tutorials on converting a concatenated string back to a numeric value:

Domo IDEAs Conference - Beast Modes - Number Formatting

GrantSmith

Apr 19, 2021

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.

Domo Idea Exchange - Beast Modes - Abbreviated Summary Number

GrantSmith

Apr 19, 2021

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.

MichelleH · November 2023

@Laf8991 The CONCAT function turns your summary number into text field, but alerts can only run off of number fields. What if you use a scheduled report instead of an alert?

jessdoe · November 2023

Grant has several tutorials on converting a concatenated string back to a numeric value:

Domo IDEAs Conference - Beast Modes - Number Formatting

GrantSmith

Apr 19, 2021

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.

Domo Idea Exchange - Beast Modes - Abbreviated Summary Number

GrantSmith

Apr 19, 2021

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.

Cards, Dashboards, Stories

Cards, Dashboards, Stories

Custom Summary Number Alerts

Best Answer

Domo IDEAs Conference - Beast Modes - Number Formatting

Domo Idea Exchange - Beast Modes - Abbreviated Summary Number

Answers

Domo IDEAs Conference - Beast Modes - Number Formatting

Domo Idea Exchange - Beast Modes - Abbreviated Summary Number

Welcome!

Welcome!

Quick Links

Categories