Summary Number with 2 metrics needing commas and rounding
Hi,
I am trying to make a Summary Number for a card with a calculated field that will look like this:
1,121 Loans | $211,100,123
or even better, if possible:
1,121 Loans | $211.1M
The first number could be anywhere from 5 to 500,000. The second currency number could be anywhere from $10,000 to $1 Billion dollars.
My current formula is:
Concat(COUNT(`LoanAmount`), ' Loans | $',
Round(SUM(`LoanAmount`),0))
The result looks like this:
1121 Loans | $211100123
How can I get the desired format in there?
Thanks in advance
Best Answer
-
Hi @AJ2020
Since Domo doesn't support formatting strings yet you'll need to do it manually. You can utilize a beastmode with CONCAT and a bunch of substring manipulation like this to solve it (this is up to 12 digits / Billions but can be modified to go higher)
(Untested but should give you the general framework)
CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 12 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 10, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 11 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 9, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 10 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 8, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 9 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 8 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 6, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 7 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 5, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 6 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 4, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 5 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 3, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) = 4 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Amount`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Amount`), 0), 2, 3)
)
WHEN LENGTH(ROUND(SUM(`Amount`), 0)) <= 3 THEN
ROUND(SUM(`Amount`), 0)
WHEN SUM(`Amount`) IS NULL THEN 0
ELSE ''
END
, 'Loans | $'
, CASE WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 12 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 10, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 11 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 9, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 10 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 9 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 8 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 6, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 7 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 5, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 6 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 4, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 5 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 3, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) = 4 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`Revenue`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`Revenue`), 0), 2, 3)
)
WHEN LENGTH(ROUND(SUM(`Revenue`), 0)) <= 3 THEN
ROUND(SUM(`Revenue`), 0)
WHEN SUM(`Revenue`) IS NULL THEN 0
ELSE 'ERROR'
END
)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- 10.6K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 63 Kowaza
- 296 仲間に相談
- 649 ひらめき共有