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
-
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!**1 -
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
0 -
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!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive