Gradient Conditional Formatting
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.
Best Answers
-
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 DESCJust 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.
1 -
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.
0
Answers
-
There are options to setup conditional formatting on a column (although it's not one click)
This link should explain it well for you:
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.0 -
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
1 -
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 DESCJust 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.
1 -
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
1 -
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.0 -
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
0 -
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.
0 -
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
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive