Thousands Separator in Beast Mode

Yoon2020
Yoon2020
in Dataflows

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

 

 

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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. 

Categories