How to get Maximum (Group 2) of Minimum (Group 1)?

[LWTri]

Hello... help would be greatly appreciated!
Crystal XI.

(1) I am trying to do a complex calculation. I need to be able to determine the Maximum date (at Group level 1) based on a Minimum date (at Group level 2).

(2) I tried multiple methods.
METHOD A: Crystal won't allow me to do a Max of a Min.
METHOD B: I also tried by sorting records by OrderAction### (Ascending) in GROUP2, and creating a Running Count (reset when OrderActionStatus changes)... in order to use record count = 1 (where OrderActionStatus = "Completed") to represent earliest date (minimum date for Group 2). Crystal won't allow me to do a Max based on that formula.

GROUP1: Encounters
GROUP2: Orders


______________________________________________________________________________________________________________________

SAMPLE: I need to grab the ActionDtTm for the MINIMUM or 1st "Completed" instance/OrderAction (for GROUP2). Then, I need to grab the Maximum of these (for GROUP1).
______________________________________________________________________________________________________________________

GROUP1: Encounter 001
GROUP2: Order 001
Record1: OrderAction 001, OrderActionStatus = Ordered, ActionDtTm = 1/1/09 12:00:01AM
Record2: OrderAction 002, OrderActionStatus = InProcess, ActionDtTm = 1/1/09 01:00:01AM
Record3: OrderAction 003, OrderActionStatus = Completed, ActionDtTm = 1/1/09 02:00:01AM
Record3: OrderAction 004, OrderActionStatus = Completed, ActionDtTm = 1/1/09 03:00:01AM
Record3: OrderAction 005, OrderActionStatus = Completed, ActionDtTm = 1/1/09 04:00:01AM

Minimum Result (for GROUP2): 1/1/09 2:00:01AM is the MINIMUM ActionDtTm for Completed OrderActions (Order 001, OrderAction 003).
___________________________________________________________

GROUP2: Order 002
Record1: OrderAction 001, OrderActionStatus = Ordered, ActionDtTm = 1/1/09 01:00:01AM
Record2: OrderAction 002, OrderActionStatus = InProcess, ActionDtTm = 1/1/09 01:00:02AM
Record3: OrderAction 003, OrderActionStatus = Completed, ActionDtTm = 1/1/09 01:00:03AM
Record3: OrderAction 004, OrderActionStatus = Completed, ActionDtTm = 1/1/09 01:00:04AM

Minimum Result (for GROUP2): 1/1/09 01:00:03AM is the MINIMUM ActionDtTm for Completed OrderActions (Order 002, OrderAction 003).
___________________________________________________________

GROUP2: Order 003
Record1: OrderAction 001, OrderActionStatus = Ordered, ActionDtTm = 1/1/09 12:00:31AM
Record2: OrderAction 002, OrderActionStatus = InProcess, ActionDtTm = 1/1/09 02:00:31AM
Record3: OrderAction 003, OrderActionStatus = Completed, ActionDtTm = 1/1/09 04:00:31AM
Record3: OrderAction 004, OrderActionStatus = Completed, ActionDtTm = 1/1/09 06:00:31AM

Minimum Result (for GROUP2): 1/1/09 04:00:31AM is the MINIMUM ActionDtTm for Completed OrderActions (Order 003, OrderAction 003).
___________________________________________________________

Maximum Result (for GROUP1): 1/1/09 04:00:31AM is the MAXIMUM ActionDtTm (GROUP1) of the MINUMUM ActionDtTm for Completed OrderActions (GROUP2) --- (Encounter 001, Order 003, OrderAction 003).
______________________________________________________________________________________________________________________



(3) Tried to use formulas:

{@ActionDtTm_OACompleted} formula used in Detail section:
if OrderActionStatus = "Completed"
then ActionDtTm

{@MinActionDtTm_OACompleted} formula used in GROUP2 footer:
minimum({@ActionDtTm_Completed},{table.ORDER_ID})

{@MaxActionDtTm_OACompleted_Encouter} formula used in GROUP1 footer:
maximum({@MinActionDtTm_OACompleted},table.ENCOUNTER_ID})

(4) MAXIMUM function did not work for either methods:

METHOD A: The above Maximum formula (for GROUP1) would not work on a calculated Minimum formula (for GROUP2).

METHOD B: After sorting records by OrderAction### (Ascending) in GROUP2, and creating a Running Count (reset when OrderActionStatus changes)... in order to use record count = 1 (where OrderActionStatus = "Completed") to represent earliest date (minimum date for Group 2)... The Maximum formula (for GROUP1) also did not work on this formula (for GROUP2).

...Any ideas? Thanks!

 

[IanWaterman]

YOu can use variables

In group1 hdr
@reset
whileprintingrecords;

global datetimevar max:=date(1900,01,01 00:00:00);
global datetimevar min:=date(2100,01,01 00:00:00);

In group 2 footer

@eval

whileprintingrecords;

global datetimevar max;
global datetimevar min;

If minimum(datefield, group2field) < min then
min:=minimum(datefield, group2field);

if minimum(datefield, group2field) > max then
max:= minimum(datefield, group2field);

place this in grp1 footer
@dispMax
whileprintingrecords;

global datetimevar max;

Ian

 

[LWTri]

Ian - nice solution! Implemented the Reset, Eval and Display formulas in appropriate sections using Datetime(YYYY,MM,DD,00,00,00) instead of Date(YYYY,MM,DD 00:00:00)... and finally saw the MAXIMUM (GROUP1) of the MINIMUM (GROUP2) date that I wanted to see! Awesome solution, very excited. Now I can use this anytime I have to work MAX OF MINS, OR MINS OF MAXES in the future. Thank you VERRRY much for your help and time! LWTri