Max Date and Correlating Value to be placed in ID Group

[records333]

I developed a report that displays the correct date ranges and values with consideration of the code in the Selection Criteria. And I created fields using If statements to capture pre and post dates. I am stumped when I attempt to select only the max date and correlating value in ID group.

 

[pmax9999]

Your explanation isn't clear to me so I might have misunderstood your requirements, but try adding the following code in the Group selection formula:

{Table.Relevant_Date} = Maximum({Table.Relevant_Date}, {Table.ID_Group_Field})

If this is not what you are looking for, please explain in more detail. Sample data and the outcome you are looking for would help.

Cheers
Pete

 

[Charliy]

If you group the report nad then have a secondary sort by date, the last record will be available to place in the Group Footer.

 

[records333]

Thanks Pete, adding the code in the Group selection worked!

]

In addition I would like to capture the following;

{DATE.LAST_DATE}in [DateAdd("m", 9, {RESOURCE.LAST CLASS}) to DateAdd("m", 15, {RESOURCE.LAST CLASS})]

{DATE.LAST_DATE}in [DateAdd("m", 21, {RESOURCE.LAST CLASS}) to DateAdd("m", 24, {RESOURCE.LAST CLASS})]

I developed IF statements for the above but the results are not capturing specific time frames. Should the additional statements be added to the Group level as well?

The goal is to trend over a period of time.

 

[pmax9999]

I would be putting the code into the Record Selection formula, assuming it is only records within those two date ranges that you want to include in your report. The Record Selection formula would look something like this:

[COLOR=#F57900][Other parts of record selection formula here][/color] AND
(
	{DATE.LAST_DATE} in [DateAdd("m", 9, {RESOURCE.LAST CLASS}) to DateAdd("m", 15, {RESOURCE.LAST CLASS})]
	OR
	{DATE.LAST_DATE} in [DateAdd("m", 21, {RESOURCE.LAST CLASS}) to DateAdd("m", 24, {RESOURCE.LAST CLASS})]
)

Does that give you what you are looking for?

Cheers
Pete

 

[records333]

Hi Pete,

Your last recommendation was my first attempt and records during the specific time frame did return. Additionally, I added IF statements to group timeframes in columns; 3-6M, 9-15M, and 21-24. However, I am stumped on how to capture the last date and last value (2 different fields) when the report is grouped by patient.

The most recent attempt was placing code in the Group selection for {DATE.LAST_DATE} in [DateAdd("m", 3, {RESOURCE.LAST CLASS}) to DateAdd("m", 6, {RESOURCE.LAST CLASS})]
and it worked. So...I did that separately for each integral; 3-6M, 9-15M, and 21-24 and the records returned. Not ideal but I got what I needed and exported to excel>access and can use as tables. But that is a temporary resolve. Thoughts please..

 

[records333]

Hi Charliy,

I tried that as well and it works if I have 1 field. But I created fields using formulas for;3-6M,9-15M and 21-24M. If I group then sort, it doesn't work across the field. What is the best way to upload an example. Copy and paste not working.

 

[Charliy]

Then create formulas that are like: maximum(formula,groupfield) for each of those formulas, and place it in your group footer. That will get you the max for each.

 

[records333]

Hi Charily,

Placing a max formula in the group is working. However, the report is showing null values in each formula group; 3-6M, 9-15M, etc. How should the report be coded to remove the null values? I tried the following;

Standalone Formula IF {lab.RESULT_DATE} in [DateAdd("m", 3, {resource.LAST CLASS}) to DateAdd("m", 8, {resource.LAST CLASS})] then {lab.RESULT_DATE}

Selection Formula(
//PRE A1C
{lab.RESULT_DATE} < {resource.1ST CLASS}
)
or
(
//POST A1C
{ab.RESULT_DATE} in [DateAdd("m", 3, { resource.LAST CLASS}) to DateAdd("m", 8, { resource.LAST CLASS})]
)
or
(
//POST A1C
{ab.RESULT_DATE} in [DateAdd("m", 9, { resource.LAST CLASS}) to DateAdd("m", 15, { resource.LAST CLASS})]
)
or
(
//POST A1C
{ab.RESULT_DATE} in [DateAdd("m", 21, { resource.LAST CLASS}) to DateAdd("m", 27, { resource.LAST CLASS})]
)

 

[Charliy]

if isnull(datefield)
then <whatever you want the default to be if the field is null>
else maximum(datefield,groupfield)