Summary Number with 2 metrics needing commas and rounding

AJ2020
AJ2020 Member
in Magic ETL

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 Coach
    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!**

Answers

  • GrantSmith
    GrantSmith Coach
    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!**

Categories