Help on Beast Mode Concat Format
Current I get for a Summary 2711 Meetings and $143043062 Total Est. Budget but I am looking for the $ amount to include comma separators and two decimal. Am using a beast mode of CONCAT(COUNT(`Meeting Identifier`),' Meetings and $', SUM(`Total Estimated Budget`),' Total Est. Budget') and I am a novice and painfully stuck. Any help is apprecaited.
Best Answer
-
What we could really use here is the FORMAT() function. @DaniBoy (hint, hint)
However, as that is not an option for beastmodes, we will have to get more creative with our solution.
It's going to look something like this... but I have not had the time to test it out:
CONCAT(
COUNT(`Meeting Identifier`)
,' Meetings and $'
,left(
round(sum(`Total Estimated Budget`),2),
MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
))
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
)+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
)+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
,' Total Est. Budget'
)I did get this to work with my sample data:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Answers
-
What we could really use here is the FORMAT() function. @DaniBoy (hint, hint)
However, as that is not an option for beastmodes, we will have to get more creative with our solution.
It's going to look something like this... but I have not had the time to test it out:
CONCAT(
COUNT(`Meeting Identifier`)
,' Meetings and $'
,left(
round(sum(`Total Estimated Budget`),2),
MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
))
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
)+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
(LENGTH(round(sum(`Total Estimated Budget`),2))),3
)+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
,' Total Est. Budget'
)I did get this to work with my sample data:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
ST_-Superman- - Endless thanks - worked like a charm. Now I just need to dive in and understand it for future use. You made me and my client happy campers. Very much apprecaited. And I have submitted FORMAT as an enhancement request as I know many others have also done.
0
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 256 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 500 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 368 Beast Mode
- 19 Variables
- 483 Automate
- 101 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive