Thousands Separator in Beast Mode

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.