5 Replies Latest reply on Aug 14, 2018 8:24 AM by Ien Lu

# Filter for Max Date and Date Range

Hi,

 loan_id asof_date current_prin loan_status MAX(Date) 2 2/28/2018 800.00 IN REPAY FALSE 2 3/31/2018 750.00 IN REPAY FALSE 2 4/30/2018 700.00 IN REPAY FALSE 2 5/31/2018 650.00 IN REPAY FALSE 2 6/30/2018 600.00 IN REPAY FALSE 2 7/30/2018 550.00 IN REPAY TRUE

I was able to filter for the max date per ID using this formula {FIXED [loan_id]:max([asof_date])} = [asof_date].  However, I want to go even further and find a max date within a date range of  '02/01/18 - 06/30/18'.

How should I rewrite the formula to take the date range into consideration?

So this means 6/30/2018 would be TRUE instead of 7/30/2018.

Thanks,

• ###### 1. Re: Filter for Max Date and Date Range

Try this:

{FIXED [loan_id]:max( IF [asof_date]>= 02/01/2018  AND  [asof_date]<= 07/30/2018  THEN [asof_date] ELSE NULL END  )} = [asof_date].

• ###### 2. Re: Filter for Max Date and Date Range

Hi Len,

check the below image

1) Add the filter to context

2) Change your max calculation like below max date:

{FIXED [Loan Id]:max(

if {FIXED [Loan Id]:max([Asof Date])} <> [Asof Date]

then

[Asof Date]

ELSE

NULL

END

)

}=[Asof Date]

Hope this helps kindly mark this answer as correct or helpful so that it will help others

BR,

NB

• ###### 3. Re: Filter for Max Date and Date Range

Sorry, I made a mistake in the question. The date range should be '02/01/18 - 06/30/18. Resulting in 06/30/2018 being TRUE and 07/30/2018 being FALSE since 07/30/2018 will no longer be within the date range.

• ###### 4. Re: Filter for Max Date and Date Range

Change the calculation to below

{fixed  [loanid] : max([As of Date]}=[As of Date] it will give true to last row

• ###### 5. Re: Filter for Max Date and Date Range

Doesn't seem to work for me unfortunately. It won't filter within the specified date range.  I have attached the workbook and data file for the workbook.