Conditional Formatting Workaround in Pivot Table
Hello,
Context:
In my organization, my role is cardbuilder. I have built a pivot table and ran into some issues with conditional formatting.
In my table, I have Daily Budget and Net Cost as two columns broken out by day. Ideally, I wanted the Net Cost column to turn RED (could be font or background - just something to flag it) if Net Cost > Daily Budget for that day. Given my data set has many different "advertising campaigns" that have different daily budgets, I cannot assign a specific dollar amount in the color rules. In an ideal world, I wish I could just tell it to turn red if Net Cost > Daily Budget column in the setup. From what I read, this is just a current "limitation" on how conditional formatting works at the moment.
As a "workaround", I created a new column called Overage. It basically shows the value if net cost exceeds daily budget for that day. (see screenshot). I did research and used a beast mode someone posted here that utilizes HTML as a workaround to spit out values in designated colors in my Overage Beast mode. This is the beast mode I used:
CASE WHEN (sum(`Net Cost`) - sum(`Daily Budget`)) > 0
THEN
CONCAT('<div style="background-color:#FF0000; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#FF0000">', sum(`Net Cost`)
-
sum(`Daily Budget`) , '</a></div>')
END
for some reason I keep encountering these issues:
- No matter what color code I use in the beast mode, the result always ends up the color in my screenshot (bluish)
- I tried adding in some <font> html codes, but no go
- I set the data format for the beast mode as currency ($) with 2 decimals but it doesn't follow this.
- is this because the beast mode shows up as "text string" vs "numeric"? How would I change that?
Any guidance would be helpful and appreciated!
Best Answer
-
ohh, I just found a workaround for this. Given the limitation on html markup for the pivot table, I modified my beast mode and then enabled "show negative numbers red" in the chart settings.
I'm sure there are many ways to write this beast mode, but the change I made is in the second statement. Now, if there is an overage it will spit out a negative number and the "show negative numbers red" setting will flag it red.
CASE WHEN (sum(`Net Cost`) - (SUM(DISTINCT `Daily Budget`))) > 0
THEN (sum(distinct `Daily Budget`)) - sum(`Net Cost`)
END
screenshot attached. thanks for helping everyone. i feel mildly successful for a Friday ?
1
Answers
-
Hi @user32470
You're getting bluish text because you have a hyperlink tag (<a href...) surrounding your text causing it to default to blue. Try taking out the <a> tags.
Are you wanting the background to be red or the text to be red?
Typically I've done the following to make the text red:
CONCAT('<div><span style="color: red">', `field`, '</span></div>')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Unless they made changes AFAIK pivot tables don't have support for HTML formatting. But let me know!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
I tried that but what happens is it returns all the code starting with <DIV> in the pivot table. Per the other poster here it might just be a limitation on the pivot table. I'll try it on another type of table for my own learning but ultimately need the pivot table. I'll submit it as a product suggestion to domo. thanks all!
0 -
ohh, I just found a workaround for this. Given the limitation on html markup for the pivot table, I modified my beast mode and then enabled "show negative numbers red" in the chart settings.
I'm sure there are many ways to write this beast mode, but the change I made is in the second statement. Now, if there is an overage it will spit out a negative number and the "show negative numbers red" setting will flag it red.
CASE WHEN (sum(`Net Cost`) - (SUM(DISTINCT `Daily Budget`))) > 0
THEN (sum(distinct `Daily Budget`)) - sum(`Net Cost`)
END
screenshot attached. thanks for helping everyone. i feel mildly successful for a Friday ?
1
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
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 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