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.