Concatenating amount and number fields WHILE also formatting them?
I am trying to create this as output in a cell:
$50,000/ 2.0 year(s)
This is my code:
CONCAT(request_amount_recommended
,'/',' ',Duration in Years
,' ','year(s)')
However, when I concatenate, I'd like for amount to be in US currency format and the number to be with 1 decimal.
I get this "50000/2.014646 year(s)"
Any help will be appreciated!
Best Answers
-
My recommendation would be to do something like this.
CONCAT('$',`request_amount_recommended`/1000,'K', ' / ', ROUND(`Duration in Years`),' year(s)')
Which gets you this output.
I'm suggesting this because it's more straightforward than trying to build a beast mode that can account for variable length financial amounts. Knowing if and where to put a comma and how many commas will require a beast mode much more complex than what I'm suggesting above.
If it's very important that it's in the requested format, let me know and I can help you build that beast mode.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1 -
@AngelaO415 here you go. A few things to keep in mind. This beast mode works for whole rounded numbers, so it won't format decimal places correctly. I went ahead and configured it for up to 10million. If you want to expand it you can do so following the same pattern. If you need help expanding, let me know.
And here is the beast mode
CONCAT('$',
CASE
WHEN LENGTH(ROUND(`request_amount_recommended`)) < 4 then `request_amount_recommended` WHEN LENGTH(ROUND(`request_amount_recommended`)) = 4 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,2,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 5 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 6 then CONCAT(SUBSTRING(`request_amount_recommended`,1,3),',',SUBSTRING(`request_amount_recommended`,4,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 7 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 8 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
END
,' | ',
ROUND(`Duration in Years`),' year(s)'
)David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1
Answers
-
this is because when you’re concatenating everything together it ends up being a string and it loses the formatting. You’ll need to implement your own formatting as part of your beast mode.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
My recommendation would be to do something like this.
CONCAT('$',`request_amount_recommended`/1000,'K', ' / ', ROUND(`Duration in Years`),' year(s)')
Which gets you this output.
I'm suggesting this because it's more straightforward than trying to build a beast mode that can account for variable length financial amounts. Knowing if and where to put a comma and how many commas will require a beast mode much more complex than what I'm suggesting above.
If it's very important that it's in the requested format, let me know and I can help you build that beast mode.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1 -
Hi @david_cunningham - This is great! When I implemented it I saw that the grant amount are not as clean as $100K. Sometimes it will be $107,240 so I will need it with the comma.
0 -
Are you saying that you need the full amount? If not and it's just formatting. Here is how you can include decimals.
CONCAT('$',ROUND(`request_amount_recommended`/1000,2),'K', ' / ', ROUND(`Duration in Years`),' year(s)')
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
@david_cunningham - Yes, I need the full amount: $123,456. Thank you!!
0 -
@AngelaO415 here you go. A few things to keep in mind. This beast mode works for whole rounded numbers, so it won't format decimal places correctly. I went ahead and configured it for up to 10million. If you want to expand it you can do so following the same pattern. If you need help expanding, let me know.
And here is the beast mode
CONCAT('$',
CASE
WHEN LENGTH(ROUND(`request_amount_recommended`)) < 4 then `request_amount_recommended` WHEN LENGTH(ROUND(`request_amount_recommended`)) = 4 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,2,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 5 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 6 then CONCAT(SUBSTRING(`request_amount_recommended`,1,3),',',SUBSTRING(`request_amount_recommended`,4,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 7 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 8 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
END
,' | ',
ROUND(`Duration in Years`),' year(s)'
)David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1 -
This is great - thanks so much @david_cunningham! You are AMAZING!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive