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

  • GrantSmith
    GrantSmith Indiana 🔵
    Accepted 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
    )
Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!