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

Find more posts tagged with

Accepted answers

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

All comments

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 today

when curdate() <= date_add(`custtransdate`, interval 90 day) then 'green'
-- for dates between 120 and 270 days ago from today

when 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'
END

Regarding 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?

meadow_ryan

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

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'
END

Tweak as necessary.

meadow_ryan

what do you mean when you have 'interval 3 month', 'interval 10 month', etc.

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.

meadow_ryan

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

Alphabetically

Series 1: Green

Series 2: Red

Series 3: Yellow