Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

he_pingping
New Contributor II

Find the next date prior to the currently selected date

I have a series of dates, eg.:

  • 20160628
  • 20160627
  • 20160623
  • 20160620

If I select a particular date, I want to find the date immediately prior to that date.

For example:

  • If I select 20160628, I get 20160627. 
  • If I select 20160627, I get 20160623.
  • If I select 20160623, I get 20160620.

Obviously, "Date - 1" won't work.  Is there a way to do this in QlikView?

1 Solution

Accepted Solutions
Highlighted
antoniotiman
Honored Contributor III

Re: Find the next date prior to the currently selected date

Try

=Max({<Date={'<$(=Max(Date))'}>} Date)

5 Replies
MVP
MVP

Re: Find the next date prior to the currently selected date

You need to create DateID for this..

SET DateFormat='YYYYMMDD';

SET TimestampFormat='YYYYMMDD hh:mm:ss[.fff]';

Data:

Load * Inline

[

DateField

20160628

20160627

20160623

20160620

];

Left Join (Data)

Load Distinct DateField, AutoNumber(DateField) as DateID Resident Data

Order By DateField;

Now use below...

=Max({<DateField, DateID = {'$(=Max(DateID)-1)'}>}DateField)

galax_allu
Valued Contributor

Re: Find the next date prior to the currently selected date

Hi

use Below() or Above() function

like Below(dates) ?                           **Below(yourdatefield)

Highlighted
antoniotiman
Honored Contributor III

Re: Find the next date prior to the currently selected date

Try

=Max({<Date={'<$(=Max(Date))'}>} Date)

MVP
MVP

Re: Find the next date prior to the currently selected date

Excellent !

antoniotiman
Honored Contributor III

Re: Find the next date prior to the currently selected date

Thanks Manish.

Regards,

Antonio