Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: highlight last date if formula Post Reply Post New Topic
Author Message
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet Topic: highlight last date if formula
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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

Edited by DBlank - 26 Sep 2017 at 4:00am
IP IP Logged
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Andrewarbogast
Newbie
Newbie
Avatar

Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
Quote Andrewarbogast Replybullet Posted: 26 Sep 2017 at 5:14am
Yes
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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


Edited by DBlank - 26 Sep 2017 at 5:40am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.