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
-
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2
Categories
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 251 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 296 仲間に相談
- 649 ひらめき共有