Textbox formatting using Concat

Reply
Highlighted
White Belt

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. 

Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.