Gradient Conditional Formatting

user00820
user00820 Member
in Charting

Is there a way to do conditional formatting with colors based ont the other values in the column?  Excel has an easy way to do this with one click.

Tagged:

Best Answers

  • Valiant
    Valiant Coach
    Answer ✓

    The other option I can think of would be to create a dataflow that does that color analysis for you.

     

    You could do the following to your current table to add a 'Percentile' column:

     

    SELECT
    a.*,
    ROUND(100.0 * (SELECT COUNT(*) FROM test_table AS b WHERE b.`Values` <= a.`Values` ) / total.cnt, 1 )
    AS percentile FROM test_table AS a
    CROSS JOIN (
    SELECT COUNT(*) AS cnt
    FROM test_table) AS total
    ORDER BY percentile DESC

     

    Just replace test_table and `Values` with the name of the column holding your ranking values.

     

    When this runs, it would assign a percentile ranking and you could then use that set your color values (ie, if > 75th percentile then green, etc).

     

    Let me know if this helps.

  • Valiant
    Valiant Coach
    Answer ✓

    If you're doing multiple percentiles like that, I would break them into concurrent data transforms that each run at the same time (where each transform only returns your ID field and the percentile being calculated).

     

    This way once they are all done, you start with your base dataset and then LEFT JOIN the other completed transforms with your individual percentiles to create your final dataset.

     

    Here's a brief example:

    Dataflow 1:

    Select ID, "Percentile Calc 1" From new_kpi_sales_funnel_dataset where...

    Output as Percentile1

     

    Dataflow 2:

    Select ID, "Percentile Calc 2" From new_kpi_sales_funnel_dataset where...

    Output as Percentile2

     

    Final Dataflow:

    SELECT *, a.PercentileCalc1, b.PercentileCalc2

    FROM new_kpi_sales_funnel_dataset AS a

    LEFT JOIN Percentile1 as b on b.ID = a.ID

    LEFT JOIN Percentile2 as c on c.ID = a.ID

    where...

     

    It's a lot more development work, but should speed up the output considerably.

Answers

  • Valiant
    Valiant Coach

    There are options to setup conditional formatting on a column (although it's not one click)

     

    This link should explain it well for you:

    https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Setting_Color_Rules_for_a_Chart#Applying_Color_Rules_to_a_Table

     

    Let me know if you have any other questions


    Sincerely,

    ValiantSpur

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • user00820
    user00820 Member

    Thanks for replying.  I am afraid this option will not work for me as I need to know value before adding the condition.  I want a color for the cell compared to the other cells, not a value.  To be specific, the example you sent does this (which will not work for me)

     

    For Cells 0, 5, -5

    If >0 = Green

    If <0 = Red

    If = 0 = Yellow 

     

    The domo formatting will apply Yellow to 0, Green to 5 and Red to -5.  I need the following conditions based on whatever value is in the cells (which changes):

     

    1, 2, 3

     

    1 Red

    2 Yello

    3 Green

     

    And if the data changes tomorrow to the following, the formatting would change:

     

    4,5,6

     

    4 Red

    5 Yellow

    6 Green

     

    The available domo formatting does not offer this option (not that I can find).  Any ideas?

    Sean

     

  • Valiant
    Valiant Coach
    Answer ✓

    The other option I can think of would be to create a dataflow that does that color analysis for you.

     

    You could do the following to your current table to add a 'Percentile' column:

     

    SELECT
    a.*,
    ROUND(100.0 * (SELECT COUNT(*) FROM test_table AS b WHERE b.`Values` <= a.`Values` ) / total.cnt, 1 )
    AS percentile FROM test_table AS a
    CROSS JOIN (
    SELECT COUNT(*) AS cnt
    FROM test_table) AS total
    ORDER BY percentile DESC

     

    Just replace test_table and `Values` with the name of the column holding your ranking values.

     

    When this runs, it would assign a percentile ranking and you could then use that set your color values (ie, if > 75th percentile then green, etc).

     

    Let me know if this helps.

  • user00820
    user00820 Member

    Thank you, it works! Now it's feasible to create gradient color like formatting.

    But when I schedule the workflow to update after the input data updated, it takes more than 1 hour and finally failed. 

     

    Do you have any idea to speed it up? 

     

    Thanks,

    Jingwen

  • Valiant
    Valiant Coach

    One of the things we do that improves query performance is to try to make sure to add indexes on the tables we're querying in the transforms.

     

    Basic format is:

    ALTER TABLE tablename ADD INDEX (`columntoindex`)

    and uncheck Output Table.

     

    Here's a link where they're discussing indexing in more depth:

    https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Creating-indexes-in-mysql-data-flows/td-p/14146

     

    Hope this helps!

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • user00820
    user00820 Member

    Thanks for getting back to me fast! I have researched the Creating index article and found that adding index mainly helps when we are using join and the joined columns are indexes. However, here I am using a cross join which there is no where clause, so it seems that adding index does not help a lot.

     

    Below is my query:

     

    SELECT
    a.*,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Sessions` <= a.`Sessions` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_sessions,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Downloads` <= a.`Downloads` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_downloads,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Downloads/Sessions` <= a.`Downloads/Sessions` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_Downloads_to_Sessions,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Installs/Downloads` <= a.`Installs/Downloads` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_Installs_to_Downloads,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Carts/Installs` <= a.`Carts/Installs` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_Carts_to_Installs,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`Confs/Carts` <= a.`Confs/Carts` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_Confs_to_Carts,
    ROUND(100.0 * (SELECT COUNT(*) FROM new_kpi_sales_funnel_dataset AS b WHERE b.`TotalConfs/Sessions` <= a.`TotalConfs/Sessions` and b.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) / total.cnt, 1 )
    AS percentile_for_TotalConfs_to_Sessions
    FROM new_kpi_sales_funnel_dataset AS a
    CROSS JOIN (
    SELECT COUNT(*) AS cnt
    FROM new_kpi_sales_funnel_dataset WHERE DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()) AS total
    WHERE a.DATE BETWEEN ADDDATE(CURDATE(), INTERVAL -30 DAY) AND CURDATE()

     

    Look forward to your response!

     

    Thanks,

    Jingwen

  • Valiant
    Valiant Coach
    Answer ✓

    If you're doing multiple percentiles like that, I would break them into concurrent data transforms that each run at the same time (where each transform only returns your ID field and the percentile being calculated).

     

    This way once they are all done, you start with your base dataset and then LEFT JOIN the other completed transforms with your individual percentiles to create your final dataset.

     

    Here's a brief example:

    Dataflow 1:

    Select ID, "Percentile Calc 1" From new_kpi_sales_funnel_dataset where...

    Output as Percentile1

     

    Dataflow 2:

    Select ID, "Percentile Calc 2" From new_kpi_sales_funnel_dataset where...

    Output as Percentile2

     

    Final Dataflow:

    SELECT *, a.PercentileCalc1, b.PercentileCalc2

    FROM new_kpi_sales_funnel_dataset AS a

    LEFT JOIN Percentile1 as b on b.ID = a.ID

    LEFT JOIN Percentile2 as c on c.ID = a.ID

    where...

     

    It's a lot more development work, but should speed up the output considerably.

  • user00820
    user00820 Member

    This explanation makes sense and I did re-develop the workflow, but it already took 20 minutes to run and is still running.

     

    So my questions are:

    • Why yesterday when I first ran it, it worked, only took 31 seconds, but today it didn't work with nothing changed. Maybe the reason is Domo stability?
    • When I am in workflow, I click the 'Preview' button and the final data set pops up immediately, but it took forever to run the workflow to generate final data set. 

    Thanks! 

     

    Jingwen

This discussion has been closed.

Categories