Solved: Summary Number with 2 metrics needing commas and r...

AJ2020 · ‎05-14-2020

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

GrantSmith · ‎05-14-2020

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 the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

GrantSmith · ‎05-14-2020