Logic when displaying a date through formula 1

[rbh123456789]

CR 11.5

I have a formula (thanks to lbass in this original post,

http://www.tek-tips.com/viewthread.cfm?qid=1645788)

which creates a preferred hierarchy of dates, shown below:

//This formula checks to see if date1 exists, if it doesn't date2 is shown, if date1 and date 2 are null, date3 is shown
//@dates
if isnull({view.date1}) then
(if isnull({view.date2}) then
{view.date3} else if not isnull({view.date2}) then
{view.date2}) else{view.date1}
//

I have the report grouped by CaseID (the unique identifier), and the @dates is Summarized with MIN in the group.

The issue i am faced with is that i need to add logic to the formula so it first compares date1 and date2 (if they both exist) and display the greater (most recent) date. If one of those dates are missing, use whichever one is available (either date 1 or 2). If neither date1 or date2 are available, use date3.

Any help would be greatly appreciated, as always.

 

[Madawc]

if isnull({view.date1}) 
then if isnull({view.date2}
     then {view.date3}
     else {view.date2}
else if isnull{view.date2}
     then {view.date1}
     else if {view.date1} > {view.date2}
          then {view.date1}
          else {view.date2}

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[MCuthill]

rbh,

I think this will return what you are seeking.

{@Dates}

IF Not(IsNull({view.Date1})) AND Not(IsNull({view.Date2})) THEN
(
   IF {view.Date1}>{view.Date2} THEN {view.Date1} ELSE {view.Date2}
) ELSE
IF Not(IsNull({view.Date1})) AND IsNull({view.Date2}) THEN {view.Date1} ELSE
IF IsNull({view.Date1}) AND Not(IsNull({view.Date2})) THEN {view.Date2} ELSE
{view.Date3}

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

Madawc - Thank you for your code, however, i was having some bracket issues, maybe some are missing?

MCuthill - Thanks you, the formula runs OK, but it seems to pick the OLDest date. is this because of the MIN summary?

 

[MCuthill]

@rbh

Yes. Sorry, I missed that in your posting - please change the summary to a MAXimum. (the most recent / newest date being a maximum, Oldest being a minumum)

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

@MCuthill thank you very much. the data is about 90% perfect.

I have a scenario where date3 is being chosen incorrectly.
It seems when there is EITHER a date1 or date2 where date3 has more than 1 entry, date3 is used.

However, when there is both a date1 and date2 and multiple date3's, the formula works fine.

The issue only arises when there is EITHER date1 or date2 with multiple date3's.

Does this make sense?

 

[MCuthill]

rbh,

I think I follow what you are saying, or "mostly".

Can you please provide an outline of the report structure and some sample data? I am looking for "what data element is common" (ie: more than one date3 per what group/id).

I am thinking we need another group inside the group for which you select a the Maximum, but cannot speculate without knowing the structure.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

MCuthill, thanks again for your help.

Here is the report to give you an idea of what we are working with.

The DATE1, DATE2, DATE3 columns are in the Details section.
The @dates and caseid are in the Group

I have highlighted the troublesome records.

http://i1226.photobucket.com/albums/ee402/rbh123456789/dates_rpt.jpg

 

[MCuthill]

rbh,

Perfect! That is what I wanted to see - I assume {CaseID} is what the group level is based on?

I only have one more question:
- if more than one Date3 is encountered, how would you determine which to use? The most current, or oldest date (if they are different dates, if they are the same, as in your examples, it doesn't matter).

Please advise as to which {Date3} you would like to see and I can provide a solution.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

MCuthill,

You are correct indeed, CaseID is the field being used for grouping.

That's a good question. If more than 1 date3 is found for the same CaseID the most current one should be used. Of course, this logic is only relevant when a date1 or date2 is not available (which you already know).

Thanks again for all your help. I'm never disappointed with the help on Tek-Tips.

 

[MCuthill]

rbh,

Happy to help! I mocked up an Excel datasource with your two examples and placed the following Formula in the Group-Level (you shouldn't need to use a summary of this field)

IF Not(IsNull(Maximum ({View.Date1}, {View.CaseID}))) AND Not(IsNull(Maximum ({View.Date2}, {View.CaseID}))) THEN
(   
    IF Maximum ({View.Date1}, {View.CaseID})>Maximum ({View.Date2}, {View.CaseID}) THEN Maximum ({View.Date1}, {View.CaseID}) ELSE Maximum ({View.Date2}, {View.CaseID})
) ELSE
IF Not(IsNull(Maximum ({View.Date1}, {View.CaseID}))) AND IsNull(Maximum ({View.Date2}, {View.CaseID})) THEN Maximum ({View.Date1}, {View.CaseID}) ELSE
IF IsNull(Maximum ({View.Date1}, {View.CaseID})) AND Not(IsNull(Maximum ({View.Date2}, {View.CaseID}))) THEN Maximum ({View.Date2}, {View.CaseID}) ELSE
Maximum ({View.Date3}, {View.CaseID})

This returned:
December 13, 2010 for CaseID 2009-03445-I
March 24, 2011 for CaseID 2010-06231-I
in my sample test.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

rbh,

Note:
This is the same formula as posted prior only using the Maximum (most recent) date found for all Date fields within a CaseID group. With the syntax for using the Summaries in the evaluation, it looks alot worse than it is.

Perhaps one could assign these Maximums to variables to clean up the IF statements... but it would only be a cosmetic change.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

MCuthill,

Your code did indeed resolve the issue..!
However, now the logic for comparing date1 and date2 for the most recent date is being ignored. It appears to be taking the OLDER date (between date1 and date 2)..

 

[MCuthill]

rbh,

Can you provide an example of this? I am not sure why it would be taking the older date, but perhaps this would shed some light on it.

Thanks!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

MCuthill,

Here is an example of the issue where the Oldest date is being used:

http://i1226.photobucket.com/albums/ee402/rbh123456789/dates_v2.jpg

I also forgot to mention another criteria...sorry.
If there are multiple date1 or multiple date2, the most recent one should be displayed (just like date3, which i mentioned in my 12 Aug 11 14:39 post)

 

[MCuthill]

rbh,

When I run this scenario, a date of August 3rd, 2010 (Date 1) is returned by the {@Dates} formula, not Jan 1, 2010 (Date2). Can you please copy your formula here for review?

Thank you for the update on the criteria for date1 & date2, I had made that assumption after your 12 Aug 11 14:39 post.

Thanks rbh,

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

Thanks again,

OK - here is the @dates formula. There is no summary involved:

IF Not(IsNull({rpt_hearing_schedule_queue.sched_date})) AND Not(IsNull({rpt_hearing_schedule_queue.med_date})) THEN
(
IF {rpt_hearing_schedule_queue.sched_date}>{rpt_hearing_schedule_queue.med_date} THEN {rpt_hearing_schedule_queue.sched_date} ELSE {rpt_hearing_schedule_queue.med_date}
) ELSE
IF Not(IsNull({rpt_hearing_schedule_queue.sched_date})) AND IsNull({rpt_hearing_schedule_queue.med_date}) THEN {rpt_hearing_schedule_queue.sched_date} ELSE
IF IsNull({rpt_hearing_schedule_queue.sched_date}) AND Not(IsNull({rpt_hearing_schedule_queue.med_date})) THEN {rpt_hearing_schedule_queue.med_date} ELSE
{rpt_hearing_schedule_queue.date_add_queue}

 

[MCuthill]

rbh,

This is not the formula I last posted. Please revise your formula as per my post on 12 Aug 11 14:48

Assuming:
Date1 = {rpt_hearing_schedule_queue.sched_date}
Date2 = {rpt_hearing_schedule_queue.med_date}
Date3 = {rpt_hearing_schedule_queue.date_add_queue}
As I am unsure the exact name of CaseID, I have assumed it is: {rpt_hearing_schedule_queue.caseid}

Revised with your field names:

IF Not(IsNull(Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID}))) AND Not(IsNull(Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID}))) THEN
(   
    IF Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID})>Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID}) THEN Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID}) ELSE Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID})
) ELSE
IF Not(IsNull(Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID}))) AND IsNull(Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID})) THEN Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID}) ELSE
IF IsNull(Maximum ({Rpt_hearing_schedule_queue.Sched_date}, {Rpt_hearing_schedule_queue.CaseID})) AND Not(IsNull(Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID}))) THEN Maximum ({Rpt_hearing_schedule_queue.Med_date}, {Rpt_hearing_schedule_queue.CaseID}) ELSE
Maximum ({Rpt_hearing_schedule_queue.Date_add_queue}, {Rpt_hearing_schedule_queue.CaseID})

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

Sorry man, my last post contained the actual field/tables names from my report (which i changed to make it easier to read for the forum. sorry!) You are correct in your assumption that:

Date1 = {rpt_hearing_schedule_queue.sched_date}
Date2 = {rpt_hearing_schedule_queue.med_date}
Date3 = {rpt_hearing_schedule_queue.date_add_queue}
caseid= {rpt_hearing_schedule_queue.caseid}

I updated the @dates formula, but when i preview i receive "A summary has been specified on a non-recurring field. Detailsdates

 

[MCuthill]

rbh,

The formula is to be placed in the CaseID group level and has to be removed from the details line.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."