I give...and need assistance with MINIMUM DATE AND CONDITIONS

[records333]

The objective is to capture patients diagnosed during a specific time frame and assigned to a specific location:

Conditionsif ({CLARITY_LOC.LOC_ID} in [99999]
and
({DIAGNOSIS.DATE} >=date (2012, 11, 30))
)
then {DIAGNOSIS.DATE} else

if ({CLARITY_LOC.LOC_ID} in [88888]
and
({DIAGNOSIS.DATE} >=date (2012, 10, 29) )
)
then {DIAGNOSIS.FIRST_DATE} else

Additionally, I would like to capture the minimum date diagnosed so I grouped and added the following; {@DX DATE} = minimum({@DX DATE},{DIAGNOSIS_INFO.PAT_ID})


Selection Criteria:
{@DX DATE} in DateTime (2012, 07, 01, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) and
{diagnosis.code} like "250.**" and
{CLARITY_SER_DEPT.LINE} = 1.00 and
{@DX DATE} <> DateTime (1900, 01, 01, 00, 00, 00) and
{@Location} startswith "CLINIC "


Lastly, an additional group was added - on dx date (half year), the summaries at half year 7/2012 and 1/2013 does not equal report footer summary.

I've be brainstorming and trying various options to no avail. My next thought is to split 1st and 2nd half of FY in to subreports but I might exclude records. Recommendations Please....

 

[NickieP]

I would have 2 separate formula fields, one for diagnosis date, one for location. Then concatenate these in a 3rd field
@Diagdate&"-"&@location
group on these using specified
groups so many options will be grouped together if required, or cross tab the results.
Great if few locations and few dates/dates grouped into years.

Otherwise, try,

case statement with IF statements in formulae
select{CLARITY_LOC.LOC_ID}
case "99999","11111":if{DIAGNOSIS.DATE}>=date(2012, 11, 30)then "A" else "B"
case "88888","22222":if{DIAGNOSIS.DATE}>=date(2011, 11, 30)then "C" else if{DIAGNOSIS.DATE}>=date(2012, 11, 30)then "D" else "E"
default:"F"
allowing you to select many locs and dates(or date ranges) at once.

so "A" could be"Everyone at Dr G's clinic Q4 2012", "F" could = "Every one exc from trial" etc.

and group /cross tab on this case statement result.