5 Replies Latest reply on May 1, 2018 1:43 PM by Brian Adams

# Identify max month

I'm trying to identify the max month/year for variable Encounter_Date using formula

DATETRUNC('month',[Encounter_Date]) = DATETRUNC('month', MAX([Encouter_Date])

but it tells me I can't mix aggregate and non-aggregate arguments.

Anyone have a better way?

Thanks,

Brian

• ###### 1. Re: Identify max month

Try

{fixed:max(date(datetrunc([Encouter_Date])))}

Thanks,

Shin

• ###### 2. Re: Identify max month

Hi Brian

first to your formula - you have aggregated encounter date with Max therefore other variables would need to be aggregated - but that will not solve the rpoblem

DATETRUNC('month',[Encounter_Date]) = DATETRUNC('month', MAX([Encouter_Date])

what you formula would then do would look at a single record to determine date equaled the max - (once again a single record)

You will need to find the overall max date with something like -           Max date =   {fixed :Max([encounter date])}

now you can use that in your conditional statements like if Datetrunc('month',[encounter date]) = datetrunc('month', [max date]) then ....

depending on what comes next you still may need to aggregate the dates with attr(), min() or max()

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Identify max month

Shin,

Thanks for the reply. I tried using

DATRETRUNC('month',[Encounter_Date]) = {fixed:max(date(DATETRUNC([Encounter_Date])))}

but the second DATETRUNC function is underlined red and it tells me "DATETRUNC is being called by DATE. Did you mena (String, Datetime)?

• ###### 4. Re: Identify max month

DATE(DATETRUNC('month',[Encounter_Date])) = {fixed:max(date(DATETRUNC([Encounter_Date])))}

• ###### 5. Re: Identify max month

Jim,

Thank you for the enlightening response!

Brian