Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

13 Replies
swuehl
MVP
MVP

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
MVP
MVP

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

How do you define second nearest date?

shraddha_g
Partner - Master III
Partner - Master III

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
Not applicable
Author

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
MVP
MVP

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

swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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