Formatting a number in CONCAT

RGranada
RGranada Contributor
in Archive

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?

Ricardo Granada 

MajorDomo@Lusiaves

**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.

Comments

  • Domo_Diesel
    Domo_Diesel Domo Employee

    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 Employee

    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.

     

  • paulogaspar
    paulogaspar Member

    @RGranada serve ?

     

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

  • [Deleted User]
    [Deleted User] Member

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

  • btm
    btm Domo Employee

    Thanks @DaniBoy,

     

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

     

    Thanks,

  • RGranada
    RGranada Contributor

    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

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • CamYoung814
    CamYoung814 Member

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

  • Mrkakaka
    Mrkakaka Member

    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.

  • kshah008
    kshah008 Contributor

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

  • [Deleted User]
    [Deleted User] Member

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

Categories