Gradient Conditional Formatting

user00820

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.

BlueRooster

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.

BlueRooster

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.

BlueRooster

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

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

 

BlueRooster

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

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

BlueRooster

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

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

BlueRooster

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

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