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

[Anonymous]

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.

[swuehl]

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

[swuehl]

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

How do you define second nearest date?

[shraddha_g]

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

[sunny_talwar]

How about this

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

[Anonymous]

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)].

[sunny_talwar]

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

[swuehl]

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

[swuehl · Accepted solution]

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

[Anonymous]

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'.