highlight last date if formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22460
Printed Date: 29 Apr 2024 at 3:05am
Topic: highlight last date if formula
Posted By: Andrewarbogast
Subject: highlight last date if formula
Date Posted: 25 Sep 2017 at 9:00am
I have a list of dates (field {isp_date}) and I want to turn the last date in the data red if the difference between that date and currentdate is >180 days.
Right now I tried:
ifdatediff("d",maximum({tbl_ISP_adult_review.isp_date}),
currentdate)>180 then crred
but it didn't work.
Any ideas?
Thanks so much.
|
Replies:
Posted By: DBlank
Date Posted: 25 Sep 2017 at 10:49am
Do you want the background or the text red?
Where did you place the formula?
is the "last record" actually the max date of the data set?
Is your comparison 180 days future dates or past dates?
|
Posted By: Andrewarbogast
Date Posted: 26 Sep 2017 at 3:50am
|
I want the font red. I placed the formula in the font color x2 in the Format Editor. The last record is the most recent date and I want the comparison of the last date and current date and if the difference is greater then 180 days.
|
Posted By: DBlank
Date Posted: 26 Sep 2017 at 4:00am
I think your formula is fine other than you need an else CrBlack.
I still am not sure if the 180 days is backwards or forwards though.
In the datediff() the order of the tow dates fields would matter for that.
just add a formula field for the date diff part and you will see if you are getting pos or neg values.
if datediff("d",maximum({tbl_ISP_adult_review.isp_date}),
currentdate)>180 then crred else crblack
|
Posted By: Andrewarbogast
Date Posted: 26 Sep 2017 at 4:38am
The formula worked but it turned all the dates Red. I had to put >-180 for any of the font to change red but it changed them all. I have it grouped by PADID and the review dates are in the details section.
22317
10/3/12
5/24/13
4/30/15
5/20/16
The formula is currently:
if datediff("d",maximum({tbl_ISP_adult_review.isp_date}),currentdate)>-180 then crred else crblack
|
Posted By: DBlank
Date Posted: 26 Sep 2017 at 4:56am
|
Are you trying to flag a client record if something is out of compliance by not have a record that is in the last 180 days?
|
Posted By: Andrewarbogast
Date Posted: 26 Sep 2017 at 5:14am
Posted By: DBlank
Date Posted: 26 Sep 2017 at 5:39am
You will want to limit the Max() to the group.
The condition is a group condition and if you place the criteria in the detail section it will apply to all the rows in that group unless you limit it by a next() or previous or field = max() condition, something to tell it for the last row only. I prefer to move the condition to the group header or footer as the condition is really about the current state of the client not one particular row per se.
If you want the date in the row to be red maybe this:
If next(table.padid) <> (table.padid) and datediff("d",maximum({tbl_ISP_adult_review.isp_date},{table.padid}),currentdate)<180 then crred else crblack
Make sure the formula is set to use default values for null
|
|