Textbox formatting using Concat

user057613
user057613 Member
in Charting

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 Coach

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • AJ2020
    AJ2020 Member

    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 Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
This discussion has been closed.

Categories