Row colorization based on multiple date ranges

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
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. 
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
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 .....
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
=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)
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
The first line will need to be what i posted

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


Also what field type is [Duration]
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
[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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
I had it wrong.  wrong placement of the brackets.

CFD = ToDays([Calculated Finish Date] - Today());
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
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)
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
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)
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
Very tidy and now easily maintainable if the rules need to change.
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
Thanks again for the help on this