Formatting Numeric Values within CONCAT functions to Currency
Hi there!
In one of my graphs, I have created a few beast modes involving nested CONCAT functions so I can capture many fields in a hover text. Some of the fields in the CONCAT are numeric but I would like them displayed as currency. After running the beast modes, the numbers are displayed as a long integer with no thousand separator etc. I understand that using my quantitative fields in this way means that I cannot use the 'Display as' format option in the Analyzer. I was wondering if there is something I can do prior to the Beast Mode CONCAT, such as an ETL node or mySQL code?
Cheers
Best Answers
-
I recommend formatting it to currency with the concat function in a beast mode. If you try and do it in an ETL, then they would become strings and the card wouldn't be able to sum them when a user is interacting with the card. Here is a beast mode that will format it for you and abbreviate the millions and thousands:
CONCAT('$', (CASE WHEN INSTR(SUM(`totalreported`),'.') = 10 THEN /*xxx million*/ CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 9 THEN /*xx million*/ CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 8 THEN /*x million*/ CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 7 THEN /*xxx thousand*/ CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'K') WHEN INSTR(SUM(`totalreported`),'.') = 6 THEN /*xx thousand*/ CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'K') WHEN INSTR(SUM(`totalreported`),'.') = 5 THEN /*x thousand*/ CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'K') END))
Here are two other examples of how to do it.
https://dojo.domo.com/discussion/52677/domo-ideas-conference-beast-modes-number-formatting#latest
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
Excellent! Thank you for sharing @MarkSnodgrass and thank you for writing up the exchange articles @GrantSmith. Have a great rest of the day y'all
8
Answers
-
I recommend formatting it to currency with the concat function in a beast mode. If you try and do it in an ETL, then they would become strings and the card wouldn't be able to sum them when a user is interacting with the card. Here is a beast mode that will format it for you and abbreviate the millions and thousands:
CONCAT('$', (CASE WHEN INSTR(SUM(`totalreported`),'.') = 10 THEN /*xxx million*/ CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 9 THEN /*xx million*/ CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 8 THEN /*x million*/ CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'M') WHEN INSTR(SUM(`totalreported`),'.') = 7 THEN /*xxx thousand*/ CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'K') WHEN INSTR(SUM(`totalreported`),'.') = 6 THEN /*xx thousand*/ CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'K') WHEN INSTR(SUM(`totalreported`),'.') = 5 THEN /*x thousand*/ CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'K') END))
Here are two other examples of how to do it.
https://dojo.domo.com/discussion/52677/domo-ideas-conference-beast-modes-number-formatting#latest
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
@MarkSnodgrass beat me to it :) I’d recommend his. Those are some good articles he posted haha.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hat tip to @GrantSmith who wrote the articles, but I didn't initially call out because he is supposed to be on vacation and not on the Dojo. :)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@MarkSnodgrass apparently I can’t stay away…Can’t let you catch up :)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Excellent! Thank you for sharing @MarkSnodgrass and thank you for writing up the exchange articles @GrantSmith. Have a great rest of the day y'all
8
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive