Color rule based on category %

Options
shreeb
shreeb Member

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

Tagged:

Best Answers

  • DavidChurchman
    Answer βœ“
    Options

    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

    Please πŸ’‘/πŸ’–/πŸ‘/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ArborRose
    ArborRose Coach
    Answer βœ“
    Options

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

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    Options

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

  • shreeb
    shreeb Member
    edited April 24
    Options

    @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
    Answer βœ“
    Options

    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

    Please πŸ’‘/πŸ’–/πŸ‘/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • shreeb
    shreeb Member
    Options

    il try this and let you know @DavidChurchman

  • shreeb
    shreeb Member
    Options

    @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
    ArborRose Coach
    Answer βœ“
    Options

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

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • shreeb
    shreeb Member
    Options

    Perfect !!! Thank you @ArborRose

  • shreeb
    shreeb Member
    Options

    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
    Options

    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:

    Please πŸ’‘/πŸ’–/πŸ‘/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • shreeb
    shreeb Member
    Options

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