Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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

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

13 Replies
MVP
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
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?

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

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

How about this

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

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

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
MVP

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

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

MVP
MVP

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

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

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

Community Browser