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.

 

[rbh123456789]

MCuthill, thanks, CR was acting up. I reloaded the app and the @dates is working perfectly! I verified a couple dozen records, and the logic is working perfectly. Thank you very much!

But there is one last thing. I thought it would be an easy matter to do, but im having difficulty.

Will i be able to sort on the @dates formula?

 

[MCuthill]

rbh,

Not to my knowledge. As this is a derived field off Maximums of a group, I believe you would need a DBA to run this same logic server-side and have the results populated in a table.

If this is not an option... I personally would export the CaseID group to excel and sort there (supress all sections except the one with {CaseID} and {@Dates}).

I received the same error when I first added {@dates}, but it isn't doing it on the reload this morning -- I'd still suggest removing it from the details level, as it is not calculating at this "level" of the data.

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]

Thank you very much. I will speak to the DBA and get them to create a view from this logic.

Thanks again for all your help!