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

  • AS
    AS Coach
    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"

Answers

  • AS
    AS Coach

    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?

     

     

    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"
  • 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

     

     

  • AS
    AS Coach

    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.

    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"
  • what do you mean when you have 'interval 3 month', 'interval 10 month', etc.

  • AS
    AS Coach

    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"
  • 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

  • AS
    AS Coach
    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"
This discussion has been closed.