Textbox formatting using Concat

I am trying to do two things to my data displayed in a text box

 

1. Number formatting to include , thousand separator

2. Color format of a part of the text using <span> as recommended in another thread.

 

I could not find a clear solution for 1 and the solutions for 2 did not work (it just displayed the '<span style..."">','text','</span>' as is)

 

Thanks in advance. 

Comments

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @user057613 

     

    #1 is possible with a lengthy beast mode.

    #2 is not possible. Text cards don't support HTML markup. Only within table cards.

     

    CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 12 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 4, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 7, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 10, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 11 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 2), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 3, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 6, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 9, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 10 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 1), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 2, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 5, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 8, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 9 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 4, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 7, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 8 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 2), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 3, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 6, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 7 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 1), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 2, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 5, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 6 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 3), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 4, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 5 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 2), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 3, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 4 THEN
    	CONCAT(
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 1, 1), ',',
          SUBSTRING(ROUND(SUM(`Number Orders`), 0), 2, 3)
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) <= 3 THEN
          ROUND(SUM(`Number Orders`), 0)
    WHEN SUM(`Number Orders`) IS NULL THEN 0
    ELSE ''
    END)

     

    For good measure here's a version with abbreviated numbers

    CONCAT(CASE WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) = 10 THEN
    	CONCAT(
    		ROUND(SUM(`Number Orders`) / 1000000000, 2), 'B'
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) >= 7 THEN
    	CONCAT(
    		ROUND(SUM(`Number Orders`) / 1000000, 2), 'M'
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) >= 4 THEN
    	CONCAT(
    		ROUND(SUM(`Number Orders`) / 1000, 2), 'K'
        )
    WHEN LENGTH(ROUND(SUM(`Number Orders`), 0)) <= 3 THEN
          ROUND(SUM(`Number Orders`), 0)
    WHEN SUM(`Number Orders`) IS NULL THEN 0
    ELSE ''
    END
    )

     

    Just replace the `Number Orders` fields with your own.

  • Thank you @GrantSmith ?

     

    Yes, that is what I was looking for! ?

    However, when I try to use that new beast mode in the table, it does not show a SUM option as it recognizes it as a text field. How do I get it to calculate as a numeric field and then just display with the "M"?

     

    Thanks, Angela

  • GrantSmith
    GrantSmith Indiana 🔴

    The abbreviated beast mode is doing the SUM already for you and adding in the M (if it's in millions). Because it's adding in the extra "M" it's treating the resulting data type as a string. You shouldn't need to select the SUM aggregation from the column as it's done inside the beast mode.

This discussion has been closed.