Formatting a number in CONCAT

RGranada

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?

Domo_Diesel

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

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

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

@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

Thanks @DaniBoy,

 

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

 

Thanks,

RGranada

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

CamYoung814

@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

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

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

[Deleted User]

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