Concatenating amount and number fields WHILE also formatting them?

AngelaO415

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!

david_cunningham

@AngelaO415

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

@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)'

)

GrantSmith

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.

https://community-forums.domo.com/main/discussion/52677/domo-ideas-conference-beast-modes-number-formatting

david_cunningham

@AngelaO415

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.

AngelaO415

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.

david_cunningham

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)')

AngelaO415

@david_cunningham - Yes, I need the full amount: $123,456. Thank you!!

david_cunningham

@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)'

)

AngelaO415

This is great - thanks so much @david_cunningham! You are AMAZING!