Textbox formatting using Concat

user057613

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. 

GrantSmith

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.

AJ2020

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

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.