Formatting a number in CONCAT

Many times we use complex summary fields based on BeastModes and CONCAT, where we present various numeric information.

Is there any way to format numbers in the CONCAT statement to make them have thousand separators? Maybe using string functions it would be possible, but as the re-usability factor is a bit LOW on beastmodes we would be copy pasting a lot...

What's the best practices on this?

Comments

  • Hi @RGranada, happy to help.  I think you answered your own question, when concatenating in a beast mode for the summary number, it can be difficult to format numbers to have thousand seperators.  It is possible, but as you said the re-usability factor may be low as you may have to reconfigure the logic for different summary numbers.

     

    Best practice would be to limit how often you do this in the summary number, allowing the native formatting to take place.  But, you could use concat() and split out the number using the left() or right() functions to add in commas.  

     

    Let me know if you have any more questions about this.

  • Rich
    Rich

    domo

    💎

    Hi,

     

    Easiest way is to do an abbreviation:

     

    CASE WHEN `Value`/1000 > 1 THEN CONCATENATE(ROUND(`Value`/1000,2),’k’) ELSE CONCATENATE(`Value`,’’) END

     

    Otherwise you can take a rounded version of the thousands place for the first part, concatenate a comma for the middle, and then take the remainder of the hundreds place to concatenate on the end.

     

    Using the case statement above you could even add addiitonal evaluations for millions if your numbers are that large.

     

  • @RGranada serve ?

     

    @Rich & @Domo_Diesel any future update or improvement in this space to automate these sort of tasks predicted to be implemented soon ?

  • @paulogaspar,

     

    Here are our latest release notes which covers features to be deployed beginning tomorrow night through next Thursday night (Feb 16) across all customer environments.

     

    @btm please have a look at the question with respect to our product road map.

     

    Thanks!

  • Thanks @DaniBoy,

     

    @ckwright is our product manager over Beast Modes that can provide some better insight.

     

    Thanks,

  • All the solutions posted for this problem fall on the same trap. Hard to implement on 250 diferent summary numbers containing multiple numbers to format on them.

     

    I was hopping something on the frame work that would for exemple imitate the .NET Format method something along this line "Format("{0:(###) ###-####}", value)", this would be of great value!

     

    Thank you

  • @DomoTeam  SQL has a "FORMAT(`Value`)" option in their syntax which allows for this type of fuctionality.  I spoke to some people at #DP17 (Domopalooza) and they suggested putting this on Dojo.   Is it possible to add the format number functionality?   

  • I totally agree with you. Although format function is available in MySQL, it will be much easier if it's an option in beast mode since we probably will create different numeric format in summary section.

  • @CamYoung814 @Mrkakaka, please feel free to submit this to our Ideas section!

  • DaniBoy
    DaniBoy

    domo

    💎

    @CamYoung814 & @Mrkakaka

     

     

    Please submit this as in idea in our exchange so that others can vote it up and it can get reviewed by our product management team.

     

    Thanks!

This discussion has been closed.