building pie chart with colors from formula
I'm trying to build a pie chart where I have a list of rows with different dates('transfer date') and am trying to for that date and the number of rows display a color of green, yellow, or red on the sections of the pie chart
green being when 0-3 months after the transfer date
yellow being 4-9 months after the transfer date
red being 10 or more months after the transfer date.....
I tried doing a case statement similar to the following:
CASE
when `custtransdate` <= (`custtransdate` + 90) then 'green'
when `custtransdate` >= (`custtransdate` + 120) and `custtransdate` < (`custtransdate` + 270) then 'yellow'
when `custtransdate` >= (`custtransdate` + 300) then 'red'
END
but it then only displays everything as 'green' and how do you set the colors as specific colors, or would there be a better chart to do this of
thanks for any help provided
Best Answer
-
That's where the sort comes into play. If you include the beast mode as a sort in addition to it being the "Pie Name" value, it will force the labels to be sorted. They you can assign color to the series values, which will always be sorted the same way:
Alphabetically
Series 1: Green
Series 2: Red
Series 3: Yellow
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
The reason your beast mode is always green is because a date is always less than a date + 90, so I suspect the date logic needs an update. Are you trying to compare the current date to the custtransdate, maybe?
CASE
-- for dates within 90 days ago from todaywhen curdate() <= date_add(`custtransdate`, interval 90 day) then 'green'
-- for dates between 120 and 270 days ago from todaywhen curdate() >= date_add(`custtransdate`, interval 120 day) and curdate() < date_add(`custtransdate`, interval 270 day) then 'yellow'
-- for dates more than 300 days ago from today
when curdate() >= date_add(`custtransdate`, interval 300 day) then 'red'
ENDRegarding color, it's not always straightforward to make sure colors always stay them same, but it is possible. Domo assigns color usually based on an ordered aggregation, so whatever has the highest aggregation at the time will get the first color, and so on, unless you lock the order of the aggregation in place (alphabetically, for example), so that you can then use the configuration to assign colors to the series values. Colors are by default assigned by order of the series value, not the label in the series.
So when you're configuring the card, sort by the beast mode here so it's done alphabetically instead of green happening to have more or less than yellow or red. That's too incosistent. Then you can assign the colors in the chart properties to match the alphabetical order.
Also, make sure your beast mode doesn't miss out on days, unless that's what you really want. What about days between 90 and 120 or days between 270 and 300?
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
basically I have 3 different ranges where
green would be 0-3 months after the custtransdate
yellow would be 4-9 months after the custtransdate
red would be 10 or mor months after the custtransdate
0 -
For that I'd probably do this:
CASE
when curdate() < date_add(`custtransdate`, interval 3 month) then 'green'
when curdate() >= date_add(`custtransdate`, interval 3 month) and curdate() < date_add(`custtransdate`, interval 10 month) then 'yellow'when curdate() >= date_add(`custtransdate`, interval 10 month) then 'red'
ENDTweak as necessary.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
what do you mean when you have 'interval 3 month', 'interval 10 month', etc.
0 -
The date_add function takes parameters of a date column and a time interval. It's great because the same function can take intervals of days, weeks, months, or years. This can be especially helpful when dealing with months since months aren't all the same length. The function is programmed to deal with that.
Take, for example, a request where you want to sum sales within the last month. You could just give the logic a filter of 30 days, but that might be wrong in many cases.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
as far as the colors go how do you know how to assign the colors correctly, lets say your current card date range is for "this year" then you change it to "all time" and your colors then become dictated as you stated above based on the aggregation of the values on the card
0 -
That's where the sort comes into play. If you include the beast mode as a sort in addition to it being the "Pie Name" value, it will force the labels to be sorted. They you can assign color to the series values, which will always be sorted the same way:
Alphabetically
Series 1: Green
Series 2: Red
Series 3: Yellow
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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