concat() integers

rmbourne · May 3

Is there a way in beastmode to CONCAT() a number with anything and not lose its thousands separator?

In my beastmode, accounts_sending_campaigns_distinct and sum(`reach`) are both integers that I wanted to be displayed with thousand separators

CONCAT('


<div style="font-size:15px; color:#EDEDDA;">
  <div>
   	<span style="font-size:20px; color:#EDEDDA"><b>',

    `accounts_sending_campaigns_distinct`,

    '</b></span>


	Partners Sending Campaigns
  </div>
  <div>	

       	<span style="font-size:20px; color:#EDEDDA"><b>',

        SUM(`reach`),

        '</b></span>
    Reach<b>
  </div>

</div>


')

I tried TO_CHAR(SUM(`reach`), '9,999.99') but I don't think TO_CHAR is recognized in beastmode

david_cunningham · May 3

So this answer makes use of a Beast Mode provided by @JacobFolsom from 2018. Thanks Jacob!

This answer is set up for a max of 9 digits. You can modify it if you need to handle more than that.

An example below. Value shows the base value, formatted with commas as Domo automatically does. The next column shows the CONCAT() label, where we can see the formatting is removed. The last column shows the CONCAT() label, but using a formatting beast mode.

Value Formatted is achieved using the below beast mode from Jacob.

CONCAT('Label: ',CASE

  WHEN LENGTH(ROUND(SUM(Column))) = 9 -- 9 digits = hundreds of millions

  THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 3,3), -- second comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 8 -- 8 digits = tens of millions

  THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block

    ',',

  SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block

  )

  WHEN LENGTH(ROUND(SUM(Column))) = 7 -- 7 digits = millions

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)),5,3) -- third comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 6 -- 6 digits = hundreds of thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 4,3) -- second comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 5 -- 5 digits = tens of thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 3,3) -- second comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 4 -- 4 digits = thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3) -- second comma block

  )

    WHEN LENGTH(ROUND(SUM(Column))) < 4 -- < 4 digits = hundreds or less

    THEN ROUND(SUM(Column))

END)

Another option would be to divide your output value, and then format those abbreviated values. I.e. - instead of 100,000 | 100k, or 1,000,000 = 1,000K or 1M. That would require a different beast mode though.

david_cunningham · May 3

The short answer to the best of my knowledge is that concat() removes formatting.

However, with that being said I believe there is a way to achieve this with some logic in the beast mode based around the length of the output. Or with division and rounding. I’ll update tomorrow morning with some more specific examples once I’ve had a chance to play around with it.

david_cunningham · May 3

So this answer makes use of a Beast Mode provided by @JacobFolsom from 2018. Thanks Jacob!

This answer is set up for a max of 9 digits. You can modify it if you need to handle more than that.

An example below. Value shows the base value, formatted with commas as Domo automatically does. The next column shows the CONCAT() label, where we can see the formatting is removed. The last column shows the CONCAT() label, but using a formatting beast mode.

Value Formatted is achieved using the below beast mode from Jacob.

CONCAT('Label: ',CASE

  WHEN LENGTH(ROUND(SUM(Column))) = 9 -- 9 digits = hundreds of millions

  THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 3,3), -- second comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 8 -- 8 digits = tens of millions

  THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block

    ',',

  SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block

  )

  WHEN LENGTH(ROUND(SUM(Column))) = 7 -- 7 digits = millions

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)),5,3) -- third comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 6 -- 6 digits = hundreds of thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 4,3) -- second comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 5 -- 5 digits = tens of thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 3,3) -- second comma block

    )

  WHEN LENGTH(ROUND(SUM(Column))) = 4 -- 4 digits = thousands

    THEN

    CONCAT(

    SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block

    ',',

    SUBSTRING(ROUND(SUM(Column)), 2,3) -- second comma block

  )

    WHEN LENGTH(ROUND(SUM(Column))) < 4 -- < 4 digits = hundreds or less

    THEN ROUND(SUM(Column))

END)

Another option would be to divide your output value, and then format those abbreviated values. I.e. - instead of 100,000 | 100k, or 1,000,000 = 1,000K or 1M. That would require a different beast mode though.

rmbourne · May 6

@david_cunningham it works, this is clever. Thanks for your help.

concat() integers

Best Answer

Answers

Categories