Thousands Separator in Beast Mode

Yoon2020

Hi Everyone,

 

I'd love to add a thousands separator to the formulas below. I searched and tried the solutions out there for hours, but had no luck. I'd appreciate it if you could help me with this.

 

 

CASE

WHEN `Objective` like 'Awareness' AND `Campaign Name`like '%MOTHERS%' AND `Platform` like '%FB%' AND SUM(`impressions`) > 300002 THEN CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">',ROUND((SUM(`impressions`)),0),'</a></div>')

END

 

 

MarkSnodgrass

I'm assuming you are wanting to do this for a summary number since you can use the formatting options on a field when you drag into use if it is a numeric field. 

There a number of methods you can use to do this. If you search for custom summary number in the Dojo, you will see some other examples. Here is what I created that accounts for if the number has a decimal in it or not. My end result displays a number as $1.45M, for example. You can tweak this to have it not abbreviate and insert a comma where necessary. 

CONCAT(
  CASE WHEN INSTR(SUM(`G/U Incurred`),'.') > 0 THEN
  /*Number contains decimal places*/
  (
  CASE 
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 13 THEN /*xxx billion*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'B')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 12 THEN /*xx billion*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'B')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 11 THEN /*x billion*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'B')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 10 THEN /*xxx million*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'M')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 9 THEN /*xx million*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'M')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 8 THEN /*x million*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'M')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 7 THEN /*xxx thousand*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'K')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 6 THEN /*xx thousand*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'K')
       WHEN INSTR(SUM(`G/U Incurred`),'.') = 5 THEN /*x thousand*/ 
                               CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'K')
       END)
    ELSE
    /*Number has NO decimal places */
    (
        CASE 
            WHEN LENGTH(SUM(`G/U Incurred`)) = 12 THEN /*xxx billion*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'B')
            WHEN LENGTH(SUM(`G/U Incurred`)) = 11 THEN /*xx billion*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'B')
           WHEN LENGTH(SUM(`G/U Incurred`)) = 10 THEN /*x billion*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'B')
           WHEN LENGTH(SUM(`G/U Incurred`)) = 9 THEN /*xxx million*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'M')
            WHEN LENGTH(SUM(`G/U Incurred`)) = 8 THEN /*xx million*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'M')
           WHEN LENGTH(SUM(`G/U Incurred`)) = 7 THEN /*x million*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'M')
          WHEN LENGTH(SUM(`G/U Incurred`)) = 6 THEN /*xxx thousand*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),3),'.',SUBSTRING(SUM(`G/U Incurred`),4,2),'K')
            WHEN LENGTH(SUM(`G/U Incurred`)) = 5 THEN /*xx thousand*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),2),'.',SUBSTRING(SUM(`G/U Incurred`),3,2),'K')
           WHEN LENGTH(SUM(`G/U Incurred`)) = 4 THEN /*x thousand*/
                            CONCAT(LEFT(SUM(`G/U Incurred`),1),'.',SUBSTRING(SUM(`G/U Incurred`),2,2),'K')
        END
    )
    END
  )

G/U Incurred is the field I was evaluating in this instance. 

michaeljohnston0

🤮

ST_Superman

@Yoon2020

You just need to split up your ROUND((SUM(`impressions`)),0)

It's already inside of a CONCAT(), so you need to separate the thousands from the hundreds. Try putting this instead

ROUND(SUM(`impressions`)/1000,0),',',MOD(SUM(`impressions`),1000)

This will not add a second comma if you exceed 999,999 impressions though. It will also look like this 0,285 if there are less then 1000 impressions (285) in my example. However, your case statement seemed to require there to be more than 300,002 impressions. If you need the formula to handle a wider range of numbers, then you will need to use something more similar to what @MarkSnodgrass has suggested.

Another option, if you are using a Dashboard, is to use a "Single value" card as your "summary number". Just place this on top of your visual. You can specify if you don't want the value to be abbreviated. By default the card would show 1,640 as 1.64K. However, you can change the Divide Value By to "None" to get 1,640 to display