Conditional formatting of Columns in a table view card

jbanez-westmont
jbanez-westmont Member
in Magic ETL

We need more options to format columns in a card table view.  In particular the ability to colorize a group of columns that are in the same category, ex. Admitted 2016, Admitted 2015 Admitted 2014, Inquiry 2016 Inquiry 2015 Inquiry 2014

Best Answers

  • GTA
    GTA Domo Employee
    Answer ✓

    This is a current workaround to get this to work. I wanted to make sure that you note that because this is HTML, you won't be able to export to a Powerpoint and still see the colors.

     

    The following Beast Mode examples will help you create conditional formatting on a standard (non-Sumo) table which I've used in the instances that a client needs a modified drill path table view. At present, these options are not available w/in Sumo tables but may be as development continues.

     

    The formatting is accomplished by creating an HTML string which contains a DIV element where the background color changes based on user defined conditions. The three colors are "Domo" colors as well so it wil keep a consistent user experience.

     

    Green Background (#bbe491):

     

    CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Green Conditional Format', '</a></div>')

     

    Yellow Background (#fccf84):

     

    CONCAT('<div style="background-color:#fccf84; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Yellow Conditional Format', '</a></div>')

     

    Red Background (#fcbcb7):

     

    CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Red Conditional Format', '</a></div>')

     

    Note: The <a> element with a reference to # prevents the user from drilling on that column as it would create a filter for the full HTML string.

     

    Working Example:

     

    CASE

    WHEN `CHANGE` > 0 THEN CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    WHEN IFNULL(`CHANGE`, 0) = 0 THEN CONCAT('<div style="background-color:#fccf84; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    WHEN `CHANGE` < 0 THEN CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    END

     Screen Shot 2015-01-23 at 1.35.33 PM.png

     

    Note: Keep in mind that the value this Beast Mode returns is a STRING, any aggregations must be done w/in Beast Mode as aggregations will be disabled when building cards.

  • GTA
    GTA Domo Employee
    Answer ✓

    If you just remove out the case statement and just leave the CONCAT, that should give you what you want. You would just create a different Beastmode for each column that applies to the category. 

    Screen Shot

  • Canioagain
    Canioagain Contributor
    Answer ✓

    thanks I actually did exactly that :

     

    WHEN Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`) < 0 THEN CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', Round(Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`)), '</a></div>')

Answers

  • GTA
    GTA Domo Employee
    Answer ✓

    This is a current workaround to get this to work. I wanted to make sure that you note that because this is HTML, you won't be able to export to a Powerpoint and still see the colors.

     

    The following Beast Mode examples will help you create conditional formatting on a standard (non-Sumo) table which I've used in the instances that a client needs a modified drill path table view. At present, these options are not available w/in Sumo tables but may be as development continues.

     

    The formatting is accomplished by creating an HTML string which contains a DIV element where the background color changes based on user defined conditions. The three colors are "Domo" colors as well so it wil keep a consistent user experience.

     

    Green Background (#bbe491):

     

    CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Green Conditional Format', '</a></div>')

     

    Yellow Background (#fccf84):

     

    CONCAT('<div style="background-color:#fccf84; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Yellow Conditional Format', '</a></div>')

     

    Red Background (#fcbcb7):

     

    CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', 'Red Conditional Format', '</a></div>')

     

    Note: The <a> element with a reference to # prevents the user from drilling on that column as it would create a filter for the full HTML string.

     

    Working Example:

     

    CASE

    WHEN `CHANGE` > 0 THEN CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    WHEN IFNULL(`CHANGE`, 0) = 0 THEN CONCAT('<div style="background-color:#fccf84; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    WHEN `CHANGE` < 0 THEN CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', `CHANGE`, '</a></div>')

    END

     Screen Shot 2015-01-23 at 1.35.33 PM.png

     

    Note: Keep in mind that the value this Beast Mode returns is a STRING, any aggregations must be done w/in Beast Mode as aggregations will be disabled when building cards.

  • jbanez-westmont
    jbanez-westmont Member

    Close enough though this will make my pure-play user squirm since it's not really configurable.  We just need the full column a single color so all the columns with the title "Admitted" will be the same column.

  • GTA
    GTA Domo Employee
    Answer ✓

    If you just remove out the case statement and just leave the CONCAT, that should give you what you want. You would just create a different Beastmode for each column that applies to the category. 

    Screen Shot

  • kshah008
    kshah008 Contributor

    @jbanez-westmont, tagging you in case you haven't seen GTA's last response.

     

    If GTA's post answered your question, please mark it as the solution!

  • jbanez-westmont
    jbanez-westmont Member

    Resolved.  Thank you.

  • Canioagain
    Canioagain Contributor

    This is great!  I just used it and added a Sum statement to it

     

    CASE

    WHEN Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`) > 0 THEN CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`), '</a></div>')

    WHEN IFNULL(Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`), 0) = 0 THEN CONCAT('<div style="background-color:#fccf84; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`), '</a></div>')

    WHEN Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`) < 0 THEN CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`), '</a></div>')

    END

     

    Only problem is I'm getting a decimal place in my answer.  Any clue on how to use a Round in here to just give me a whole number?

  • Jason
    Jason Member

    In your CONCAT, throw a ROUND function around the resulting formula, and round to 0 places. For example: 

     

    CASE

    WHEN Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`) > 0 THEN CONCAT('<div style="background-color:#bbe491; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', ROUND(Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`), 0) '</a></div>')

     

     

  • Canioagain
    Canioagain Contributor
    Answer ✓

    thanks I actually did exactly that :

     

    WHEN Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`) < 0 THEN CONCAT('<div style="background-color:#fcbcb7; width: 100%; height:100%; margin:-20px; padding:20px"><a href="#">', Round(Sum(`Returns`+ `In Yard` ) - Sum(`Committed` + `Unuseable`)), '</a></div>')

  • kd1985
    kd1985 Member

    This solution doesn't work in "Pivot View" table card.

    Any idea about implementing conditional formatting in pivot views?

     

  • user079294
    user079294 Member

    I am trying to use beast – mode to setup some html formats however it is not working for me, below is my code and output snippet for your reference:

     

    Code line: 

    <div style="color:green; font-weight:bold; margin:-20px; padding:20px">10.5631</div>

    While tested on normal HTML page it worked perfectly.

Categories