Conditional formatting based on last contact in weeks 1

[n2nuk]

Hi there,

I need to compile a report that changes the background of the client record based on when they were last contacted.

Client last seen: 12 or more weeks ago - red
Client last seen: 10 - 12 weeks ago - amber/orange
Client last seen: 9 weeks ago - Yellow
Client last seen: less than 8 weeks ago - green

I have been looking at the date diff command as this works out weeks but am unsure as to how to use on my report. I would really appreciate a few pointers/ideas to get me started.

I am using CR9.

Many thanks

Naz

 

[MisterMo]

Here is a sample of the code you could use

if DateDiff ("W", {Table.Date}, currentdate) < 1 then
red
else if DateDiff ("W", {Table.Date}, currentdate) = 1 then
blue
else if DateDiff ("W", {Table.Date}, currentdate) = 2 then
green
else
Black

Mo

 

[MisterMo]

Place this formula on the background field or section of your choice, providing that is fitting with the date you are checking out

numberVar Wks;

Wks := DateDiff ("W", {DCD.NextCallDate}, currentdate);

//Client last seen: 12 or more weeks ago - red
//Client last seen: 10 - 12 weeks ago - amber/orange
//Client last seen: 9 weeks ago - Yellow
//Client last seen: less than 8 weeks ago - green

if Wks >= 12 then
color(255,0,0)
else if Wks >= 10 and Wks < 12 then
color(255,128,64)
else if Wks = 9 then
color(255,255,128)
else
color(0,255,0)

Mo

 

[MisterMo]

Sorry I've messed things up a little

this is the last one....... I hope.

numberVar Wks;

Wks := DateDiff ("D", currentdate, {Table.DateField});

//Client last seen: 12 or more weeks ago - red
//Client last seen: 10 - 12 weeks ago - amber/orange
//Client last seen: 9 weeks ago - Yellow
//Client last seen: less than 8 weeks ago - green

if Wks >= 12 then
color(255,0,0)
else if Wks >= 10 and Wks < 12 then
color(255,128,64)
else if Wks = 9 then
color(255,255,128)
else
color(0,255,0)

Mo

 

[n2nuk]

Hi Mo,

Thanks for the updates, I think I have a problem.
I have pasted the code from your latest update and amended the table.field value to the date column from my database.

when I run the report all the rows are coloured green, I have checked out some of the dates from the dataset and have contacts that were last seen in December of 2004.

I have updated the DateDiff "D" to "W" but this has not made any difference any ideas?

Thanks for all your support, much appreciated.

Naz

 

[MisterMo]

can you create a formula and paste the following

DateDiff ("W", currentdate, {Table.DateField})

this should give you the Number of weeks, place it on the section where you want to change the colour and see what you get

Mo

 

[lbass]

It should be:

numberVar Wks;

Wks := DateDiff ("w", {Table.DateField},currentdate)

If you use currentdate as the middle argument, all results will be negative and therefore will default to green.

-LB

 

[n2nuk]

Thanks for all your assistance MisterMo.
When I pasted the formula into the report, the problem became apparent, I was getting minus numbers, hence the columns showing up as green.
I swapped over the 2 arguments in the date command all now works well.

I now have a handy column on the report that displays the number of weeks the client was last seen!

Once again many thanks for your support.

Regards

Naz