formatting in a table
I have a table like this:
Category | Actual | Budget
Revenue | 4.50 | 5.50
Sales | 4,250.00 | 5,550.00
For this table, I have in a pivot table, and I want the revenue to be formatted with the ($), and the sales, I want to not have decimals. How can I do this in a beast mode? If I format this for the column on the card it will format everything as one format.
Best Answers
-
Trying to apply a number format in a beast mode can be limiting, especially if you want to comma separate the thousands. It might be beneficial to restructure your data in an ETL so that Revenue and Sales are in their own columns.
0 -
There's no beast mode command for dealing with commas, but you can achieve it in other ways if desired.
CASE
WHEN `Category` = 'Revenue' THEN
CONCAT('$',
CASE
WHEN ROUND(`Actual`, 2) >= 1000000 THEN CONCAT(ROUND(ROUND(`Actual`, 2) / 1000000, 2), 'M')
WHEN ROUND(`Actual`, 2) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 2) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 2), 1000), 2), 3, '0'))
ELSE ROUND(`Actual`, 2)
END
)
WHEN `Category` = 'Sales' THEN
CASE
WHEN ROUND(`Actual`, 0) >= 1000000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000000), 'M')
WHEN ROUND(`Actual`, 0) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 0), 1000), 0), 3, '0'))
ELSE ROUND(`Actual`, 0)
END
ELSE ROUND(`Actual`, 2)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
How about something like
CASE
WHEN `Category` = 'Revenue' THEN CONCAT('$', FORMAT(`Actual`, 2))
WHEN `Category` = 'Sales' THEN FORMAT(`Actual`, 0)
ELSE FORMAT(`Actual`, 2)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
I keep getting this error.
0 -
Sorry…I'm not in Domo testing the function. I'll create a quick sample set and test.
CASE
WHEN `Category` = 'Revenue' THEN CONCAT('$', ROUND(`Actual`, 2))
WHEN `Category` = 'Sales' THEN ROUND(`Actual`, 0)
ELSE ROUND(`Actual`, 2)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Trying to apply a number format in a beast mode can be limiting, especially if you want to comma separate the thousands. It might be beneficial to restructure your data in an ETL so that Revenue and Sales are in their own columns.
0 -
I think this may be the answer although it's disappointing I can't do it this way.
0 -
There's no beast mode command for dealing with commas, but you can achieve it in other ways if desired.
CASE
WHEN `Category` = 'Revenue' THEN
CONCAT('$',
CASE
WHEN ROUND(`Actual`, 2) >= 1000000 THEN CONCAT(ROUND(ROUND(`Actual`, 2) / 1000000, 2), 'M')
WHEN ROUND(`Actual`, 2) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 2) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 2), 1000), 2), 3, '0'))
ELSE ROUND(`Actual`, 2)
END
)
WHEN `Category` = 'Sales' THEN
CASE
WHEN ROUND(`Actual`, 0) >= 1000000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000000), 'M')
WHEN ROUND(`Actual`, 0) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 0), 1000), 0), 3, '0'))
ELSE ROUND(`Actual`, 0)
END
ELSE ROUND(`Actual`, 2)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
I will try this. Does this approach work for removing decimal places as well? Also when I use format in beast modes i get syntax errors as it indicates Domo does not support this function.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 99 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 727 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive