topic Re: Issue with dates in App Development

Issue with dates

Dud-As — Mon, 12 Jul 2021 11:16:21 GMT

Hello everyone,

I have an Excel file with the following data:
-User Id
-Visit Date
-Visit Type (It is a number, with values from 1 to 5)

I need to get the one record for each user, for each type of visit and with the second most recent date (not the most recent visit, the previous one).

If I have the following data:
UserId VisitDate VisitType
1000 12/07/2021 1
1000 11/07/2021 2
1000 10/07/2021 1
1000 09/07/2021 2

I need as a result only:

1000 10/07/2021 1
1000 09/07/2021 2

How can I do this with QLIK?

Thanks in advance!

Re: Issue with dates

Chanty4u — Mon, 12 Jul 2021 16:04:12 GMT

If you just want to select second last date then

=max(VisitDate,2)

=Firststoredvalue(VisitDate,-2)

Re: Issue with dates

Dud-As — Tue, 13 Jul 2021 06:54:56 GMT

Thank you for your quick response!

We have a VisitId for each of those records, would it be possible to know which VisitId is the one of the result =max(VisitDate,2)?

In order to identify the VisitId of the second last date.

Thanks again!

Re: Issue with dates

tresB — Tue, 13 Jul 2021 07:02:24 GMT

Use firstsortedvalue() and pass rank value 2 (third parameter) for second most recent date, like:

Firststoredvalue( VisitType, -VisitDate, 2)

Re: Issue with dates

Dud-As — Tue, 13 Jul 2021 07:34:59 GMT

It works!

Thank you both!