Color rule based on category %

shreeb

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 !!

DavidChurchman

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
when Category = 1 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) <0.05 then concat(sum(Orders),' 🔴')
when Category = 4 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) >0.5 then concat(sum(Orders),' 🔵')
else concat(sum(Orders),' ⚪')
end

You 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

ArborRose

You can Google "emoji" to find sites and catalogs. This one might help.

https://emojidb.org/circle-color-emojis

ArborRose

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

shreeb

@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.

DavidChurchman

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
when Category = 1 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) <0.05 then concat(sum(Orders),' 🔴')
when Category = 4 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) >0.5 then concat(sum(Orders),' 🔵')
else concat(sum(Orders),' ⚪')
end

You 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

shreeb

il try this and let you know @DavidChurchman

shreeb

@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!!

ArborRose

You can Google "emoji" to find sites and catalogs. This one might help.

https://emojidb.org/circle-color-emojis

shreeb

Perfect !!! Thank you @ArborRose

shreeb

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?

DavidChurchman

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
when Category = 1 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) <0.05 then concat(sum(Orders), ' ', % of total, ' 🔴')
when Category = 3 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) >0.1 then concat(sum(Orders), ' ',% of total, ' 🔵')
when Category = 4 and sum(Orders)/sum(sum(Orders) fixed(by week No.)) >0.5 then concat(sum(Orders), ' ',% of total, ' 🔵')
else concat(sum(Orders),' ',% of total, ' ⚪')
end

You 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:

shreeb

I was able to make the emojis work !!! Thanks a lot @david_cunningham !! Thanks @ArborRose and @DavidChurchman !