Discussions

Expand all | Collapse all

Row colorization based on multiple date ranges

QuickBaseCoach Dev./Training12-19-2017 22:43

Chad Brandmire12-19-2017 22:50

  • 1.  Row colorization based on multiple date ranges

    Posted 12-15-2017 22:16
    How would I colorize rows in a table chart based on multiple work date ranges?
    i.e.
    I have many tasks for a project each with different durations and finish dates. I want to colorize based on % complete based on a point in time to the finish date dependent on duration. 


  • 2.  RE: Row colorization based on multiple date ranges

    Posted 12-15-2017 22:53
    Chad, can you say in words what the rules are for coloring and I will try to help you.  List the conditions in the order that you want then evaluated.
    for example

    if this ......, then blue,
    else if those other conditions ....., then green,
    else if yet another test is true , then red .....


  • 3.  RE: Row colorization based on multiple date ranges

    Posted 12-15-2017 23:00
    If calculated finish day is <= 3 days away and duration is <=10 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

    If calculated finish day is <= 10 days away and duration is <=30 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

    If calculated finish day is <= 30 days away and duration is <=90 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.

    If calculated finish day is <= 35 days away and duration is >=91 and %complete is < 50 then red, if < 70 then orange, if < 85 then yellow.


  • 4.  RE: Row colorization based on multiple date ranges

    Posted 12-16-2017 00:00
    =We can try this, but it is not tested and its not easy to type perfect code with out the Syntax editor to help check for errors.

    var number CFD = ToDays([Calculated Finish Date] - Today();
    var number Dur = [Duration field in days];
    var number PC = [Percent complete] *10

    var text Red = "#FFAEB9";
    var text Yellow = "yellow";
    var text Orange = "#fda302";
    // laura has a great tool here https://laurahillier.quickbase.com/db/bhy8pumtp

    IF(
    $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
    $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
    $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

    $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
    $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
    $CFD < 10and $Dur < 30 and $PC < 85, $Yellow,

    $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
    $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
    $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

    $CFD < 35 and $Dur > 90 and  $PC < 50, $Red,
    $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
    $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)


  • 5.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 15:47
    Thanks for the reply.

    I made a couple of slight changes. But, still can't seem to get this to work. I'm getting a Formula Error

    -- Incorrect variable type
    The type of the variable does not match the return type of the expression.

    I'm having trouble placing where this error is occuring



    var number CFD = ToDate([Calculated Finish Date]) - Today();
    var number Dur = [Duration];
    var number PC = [% Complete] *10;

    var text Red = "#FFAEB9";
    var text Yellow = "yellow";
    var text Orange = "#fda302";

    If(
    $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
    $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
    $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

    $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
    $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
    $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

    $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
    $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
    $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

    $CFD < 35 and $Dur >90 and $PC < 50, $Red,
    $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
    $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)


  • 6.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 15:54
    The first line will need to be what i posted

    var number CFD = ToDays([Calculated Finish Date]) - Today();


    Also what field type is [Duration]


  • 7.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 16:01
    [Duration] is a numeric field 

    [Calculated Finish Date] is a  formula -work date 

    I get an error with ToDays
    Formula error -- Bad or missing arguments in function call

    I'm guessing the [Calculated Finish Date] field is the bad guy here. It's been a thorn in side trying to figure this out


  • 8.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 16:09
    I had it wrong.  wrong placement of the brackets.

    CFD = ToDays([Calculated Finish Date] - Today());


  • 9.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 16:20
    That was the spark I needed. Thanks! Still have some tweaking to do to get the colorization right though. Not showing the yellows or oranges...

    var number CFD = ToDays(ToDate([Calculated Finish Date]) - Today());
    var number Dur = [Duration];
    var number PC = [% Complete] *10;

    var text Red = "#FF3030";
    var text Yellow = "yellow";
    var text Orange = "#fda302";

    If(
    $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
    $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
    $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

    $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
    $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
    $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

    $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
    $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
    $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

    $CFD < 35 and $Dur >90 and $PC < 50, $Red,
    $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
    $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)


  • 10.  RE: Row colorization based on multiple date ranges

    Posted 12-18-2017 17:34
    Got it! Grrr math

    var number CFD = ToDays(ToDate([Calculated Finish Date]) - Today());
    var number Dur = [Duration];
    var number PC = [% Complete] *100;
    var number SD = ToDays(ToDate([Start]) - Today());

    var text Red = "#FF3030";
    var text Yellow = "yellow";
    var text Orange = "#fda302";

    If(
    $SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 50, $Red,
    $SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 70, $Orange,
    $SD < 1 and $CFD < 3 and $Dur < 10 and $PC < 85, $Yellow,

    $SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 50, $Red,
    $SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 70, $Orange,
    $SD < 1 and $CFD < 10 and $Dur < 30 and $PC < 85, $Yellow,

    $SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 50, $Red,
    $SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 70, $Orange,
    $SD < 1 and $CFD < 30 and $Dur < 90 and $PC < 85, $Yellow,

    $SD < 1 and $CFD < 35 and $Dur >90 and $PC < 50, $Red,
    $SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
    $SD < 1 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)


  • 11.  RE: Row colorization based on multiple date ranges

    Posted 12-19-2017 22:41
    For anyone still following this... I hope this is the finished product. Note I did change some field types...

    var number CFD = ToDays([Assigned Finish Date] - Today());
    var number Dur = [Calc Duration];
    var number PC = [% Complete] *100;
    var number SD = ToDays(ToDate([Start]) - Today());

    var text Red = "#f75d59";
    var text Yellow = "yellow";
    var text Orange = "#fda302";

    If(
    $SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 50, $Red,
    $SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 70, $Orange,
    $SD < 0 and $CFD < 3 and $Dur <= 10 and $PC < 85, $Yellow,

    $SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 50, $Red,
    $SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 70, $Orange,
    $SD < 0 and $CFD < 5 and $Dur <= 20 and $Dur > 11 and $PC < 85, $Yellow,

    $SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 50, $Red,
    $SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 70, $Orange,
    $SD < 0 and $CFD < 10 and $Dur <= 30 and $Dur > 21 and $PC < 85, $Yellow,

    $SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 50, $Red,
    $SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 70, $Orange,
    $SD < 0 and $CFD < 30 and $Dur <= 90 and $Dur > 31 and $PC < 85, $Yellow,

    $SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 50, $Red,
    $SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 70, $Orange,
    $SD < 0 and $CFD < 35 and $Dur > 90 and $PC < 85, $Yellow)


  • 12.  RE: Row colorization based on multiple date ranges

    Posted 12-19-2017 22:43
    Very tidy and now easily maintainable if the rules need to change.


  • 13.  RE: Row colorization based on multiple date ranges

    Posted 12-19-2017 22:50
    Thanks again for the help on this