# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

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

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

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

13 Replies
MVP

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

MVP

## 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?

Honored Contributor III

## 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?

MVP

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

How about this

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

New Contributor II

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

MVP

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

MVP

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

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

MVP

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

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

New Contributor II

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