| Author |
Message |
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
Posted: 26 Sep 2017 at 5:14am |
|
Yes
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
|
|
