13 Replies Latest reply: Sep 29, 2017 12:14 AM by Rex Liao

# How to get the 2nd nearest date without using Max(Date,2)?

Dear all,

Just have a question to get the 2nd nearest date without using Max(Date,2).

Currently, I use the following syntax to get Sum(Shares) on the nearest date, and it woks fine.

Sum(Aggr(Sum({\$< Report_Date={"\$(     =Max(Report_Date)     )"} >} Shares), Security_ID) )

However, when I tried to get the 2nd nearest date without using Max(Date,2) for some concerns, I used the following syntax

Max({<  Report_Date={     "<=\$(=Date(Max(Report_Date)-1,'YYYY/MM/DD'))"     } >} Report_Date)

to replace

Max(Report_Date)

But it didn't work, is anyting wrong here?

It's appreciated if someone can help me out here.

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

What's the format of Report_Date field?

You are using a numeric search on that field using <= hence

a) you will probably select more than one date

b) if your date value in format YYYY/MM/DD is interpreted as number calculation, this will result probably result in no records returned

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

And what are your concerns using Max(Report_Date,2)?

How do you define second nearest date?

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

Actuall, I am trying to make a time series table to show the change of Sum(Shares) between each day.

For example,

Day       Sum(Shares)            subtract to the previous day

T            100                           20

T-1         80                             -30

T-2         110

If I use max(date, 2), I only can get [T - (T-1)], but cannot get [(T-1) - (T-2)].

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

What about chart inter record functions, like Above() / Below()

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

=Sum(Shares) - Below( Sum(Shares) )

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

Thanks, it works well in char.

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

The data format should be same as Access format as "yyyy/m/d".

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

is 'YYYY/MM/DD' format is same as that of values in Report_Date Field?

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

Report_Date field is retried from Access DB, and the format in Access is "yyyy/m/d".

So I think it is  'YYYY/MM/DD'.

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

How about this

Max({<Report_Date = {"<\$(=Max(Report_Date))"}>} Report_Date)

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

So, something along these lines

Sum({\$< Report_Date={"\$(=Max({<Report_Date = {""<\$(=Max(Report_Date))""}>} Report_Date))"}>}Aggr(Sum({\$< Report_Date={"\$(=Max({<Report_Date = {""<\$(=Max(Report_Date))""}>} Report_Date))"}>} Shares), Security_ID))

Note the use of double "double quotes" to use Escape sequences

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

Thanks for the lines, just realised the error may be caused by "".

However, I didn't succeed by using the line above, but it works OK with the following lines,

but I have no idea why it works.

Sum(Aggr(Sum({\$< Report_Date={"\$(=Max({<Report_Date={'<\$(=Max(Report_Date))'}>} Report_Date))"} >} Shares)

, Security_ID) )

• ###### Re: How to get the 2nd nearest date without using Max(Date,2)?

Just an aside, but I never knew max()  had a rank parameter.

- Thanks for enlightening me.