Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
=Sum(Shares) - Below( Sum(Shares) )
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
And what are your concerns using Max(Report_Date,2)?
How do you define second nearest date?
is 'YYYY/MM/DD' format is same as that of values in Report_Date Field?
How about this
Max({<Report_Date = {"<$(=Max(Report_Date))"}>} Report_Date)
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)].
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
What about chart inter record functions, like Above() / Below()
=Sum(Shares) - Below( Sum(Shares) )
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'.