Color rule based on category %
Hello,
I am trying to find a way to add color rules based on category.
This is an example - this is a pivot table showing all the orders based on category by week.
Each category has a different range that needs a color rule
say for category 1 - it should be less than 5% to show green else red
but for category 2 - if its below 10% then green else red
Similarly, i want to be able to apply color rules based on the total % value for each category
Thanks !!
Best Answers
-
Pivot cards don't play well with HTML formatting, but they can accept unicode or emojis, which can serve as indicators that you can manipulate in a case statement.
For example, I made this pivot card:
Using this case statement:
case
whenCategory
= 1 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) <0.05 then concat(sum(Orders
),' π΄')
whenCategory
= 4 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) >0.5 then concat(sum(Orders
),' π΅')
else concat(sum(Orders
),' βͺ')
endYou could build out your cases for each situation, using whatever emoji/unicode character you want. One caution, not all browsers display unicode/emojis in the same way, so depending on what you choose as your indicator, you're losing some control of the final product
Please π‘/π/π/π this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
You can Google "emoji" to find sites and catalogs. This one might help.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
Using an html table chart typeβ¦you can create an html style. This one is nonsenseβ¦but should give you enough to create your field.
CASE category
when 'category1' then CONCAT('<span style="color:green;background-color:#ff0000;">',col1,'</span>')
when 'category2' then CONCAT('<span style="color:purple;background-color:#00ff00;">',col1,'</span>')
else col1
end** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose I tried that, but the user would have to scroll so much. I am trying to make it easier for the user to use. hence needed a pivot table.
0 -
Pivot cards don't play well with HTML formatting, but they can accept unicode or emojis, which can serve as indicators that you can manipulate in a case statement.
For example, I made this pivot card:
Using this case statement:
case
whenCategory
= 1 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) <0.05 then concat(sum(Orders
),' π΄')
whenCategory
= 4 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) >0.5 then concat(sum(Orders
),' π΅')
else concat(sum(Orders
),' βͺ')
endYou could build out your cases for each situation, using whatever emoji/unicode character you want. One caution, not all browsers display unicode/emojis in the same way, so depending on what you choose as your indicator, you're losing some control of the final product
Please π‘/π/π/π this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
il try this and let you know @DavidChurchman
0 -
@DavidChurchman - I was able to get the emojis to work on edge.
Can you help me find more options on the emojis other than the red and blue circles? I tried using it from my MS teams but i was not able to copy them into my beast mode.Thanks!!
0 -
You can Google "emoji" to find sites and catalogs. This one might help.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Perfect !!! Thank you @ArborRose
0 -
Since I have sensitve information, is it possible to connect with someone who can help me with this? I cant bring in all the calculations and make it look pretty. Maybe if i connected with someone i could get an extra set of eyes?
0 -
Maybe you can use a fake dataset and post what you're seeing and talk through why it's not pretty? I've been playing with this example as a test case. I tend to find the tables in Domo to be ugly/difficult to make functional. The new border options help a fair amount, but it still feels like I have to hack it a bit to look nice. This is where I landed with your example:
In general, vertical borders are ugly. That's advice across most data viz books. (Few, Wong, Schwabish, Evergreen, etc.) The problem in Domo, is if you want to use a pivot table, the most useful table type for long data like this, and you want to have multiple measures displayed, there's not a lot of control around where the vertical borders are. Since I'm already concatting the indicator emoji in, I played with concatting all three with this beastmode. To get the columns to stay aligned, I did some work to make sure the number of characters would be the same in each column, and right-aligned the columns:
case
whenCategory
= 1 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) <0.05 then concat(sum(Orders
), ' ',% of total
, ' π΄')
whenCategory
= 3 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) >0.1 then concat(sum(Orders
), ' ',% of total
, ' π΅')
whenCategory
= 4 and sum(Orders
)/sum(sum(Orders
) fixed(byweek No.
)) >0.5 then concat(sum(Orders
), ' ',% of total
, ' π΅')
else concat(sum(Orders
),' ',% of total
, ' βͺ')
endYou could also make it more Domo-esque by not using a table. Maybe just a 100% stacked bar, with some indicators to flag your criteria:
Please π‘/π/π/π this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
2 -
I was able to make the emojis work !!! Thanks a lot @david_cunningham !! Thanks @ArborRose and @DavidChurchman !
2
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive