How do I put a comma in multiple summary numbers?

Reply
White Belt

How do I put a comma in multiple summary numbers?

How do I fix my beastmode to add commas to my dollar amounts?

 

CONCAT('Total Revenue - $',ROUND(SUM(`Revenue`)),'<br>Total Net Profit - $',ROUND(SUM(`Net Profit`)))

 

Result:

Total Revenue - $54783

Total Profit - $12455

 

But what I want to see is:

Total Revenue - $54,783

Total Profit - $12,455

 

 

Thank you,

Nicole

Red Belt
Red Belt

Re: How do I put a comma in multiple summary numbers?

You might check out this thread. It's a few years old but I think it still applies today.

I don't think there's an easy solution right now.  Because there isn't we typically just round our numbers to the nearest thousand or million, depending on the size of the business and the audience.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
White Belt

Re: How do I put a comma in multiple summary numbers?

Give this a try. You'll have to replace the inners with the fields from your database. I didn't build the code with optimization in mind, so you're welcome to tweak it as needed.

CONCAT(
CASE WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 9
	THEN
	CONCAT('$',
 	CASE 
      WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 12
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 7, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 10, 3)
              )
       WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 11
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 6, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 9, 3)
              )
      WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 10
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 5, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 8, 3)
              )
       ELSE 'UNKNOWN'
       END)  
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 6
    	THEN
        CONCAT('$',
 	CASE 
      WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 9
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 7, 3)
              )
      WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 8
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 6, 3)
              )
        WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 7
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 5, 3)
          )
       ELSE 'UNKNOWN'
       END)
WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) > 3
		THEN
		CONCAT('$',
 		CASE 
       WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 6
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 4, 3)
              )
       WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 5
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 3, 3)
              )
        WHEN LENGTH(CONCAT('',ROUND(Sum(`SALES`), 0))) = 4
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`SALES`), 0)), 2, 3)
              )
        ELSE 'UNKNOWN'
       END)
ELSE RIGHT(CONCAT('',ROUND(Sum(`SALES`), 0)),3)
END
,' || ',
CASE WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 9
	THEN
	CONCAT('$',
 	CASE 
      WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 12
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 7, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 10, 3)
              )
       WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 11
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 6, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 9, 3)
              )
      WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 10
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 5, 3)
           ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 8, 3)
              )
       ELSE 'UNKNOWN'
       END)
       
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 6
    	THEN
        CONCAT('$',
 	CASE 
      WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 9
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 7, 3)
              )
      WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 8
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 6, 3)
              )
        WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 7
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 5, 3)
          )
       ELSE 'UNKNOWN'
       END)
 
WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) > 3
		THEN
		CONCAT('$',
 		CASE 
       WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 6
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 3)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 4, 3)
              )
       WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 5
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 2)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 3, 3)
              )
        WHEN LENGTH(CONCAT('',ROUND(Sum(`PROFIT`), 0))) = 4
            THEN
        CONCAT(
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 1, 1)
          ,',',
            SUBSTRING(
                CONCAT('',ROUND(Sum(`PROFIT`), 0)), 2, 3)
              )
        ELSE 'UNKNOWN'
       END)
ELSE RIGHT(CONCAT('',ROUND(Sum(`PROFIT`), 0)),3)
END
)              

  

Highlighted
White Belt

Re: How do I put a comma in multiple summary numbers?

Marc_H, doesn't seem like it should be this challenging to do something as simple & routine as formatting a number string, but dang - you sure saved me with this!  Our DOMO users are going to appreciate you proving a 'template' for this.  Thank you sir!

Pete Lardner
Domo Master - Lee Enterprises, Inc
Announcements
Call for Presenters! We want to hear your Domo story, submit your entry and win a free pass to Domopalooza 2019!. Click here!