Concatenating amount and number fields WHILE also formatting them?

AngelaO415
AngelaO415 Member
edited May 7 in Beast Mode

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

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    image.png

    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! ✔️**

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    image.png

    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! ✔️**

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    image.png

    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! ✔️**

  • AngelaO415
    AngelaO415 Member

    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
    david_cunningham Coach

    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)')
    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • AngelaO415
    AngelaO415 Member
    edited May 7

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

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @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.

    image.png

    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! ✔️**

  • AngelaO415
    AngelaO415 Member

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

Categories