Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor

## How to use Peek/Previous for multiple dimension values?

Dear all,

Can anyone help me out with a challenge?

I need the max date for each Name but with the condition that if that max date is higher than today I need the next date.

For each Name I need only 1 date in the output.

Input

Table1

Name,Date
A,2-9-2023
A,30-8-2023
A,25-1-2023
B,1-1-2023
B,5-5-2022

Expected Output

A,30-8-2023
B,1-1-2023

My idea was to use an expression like

distinct Name,

If(Peek(Date)>today(),previous(Date),Peek(Date)

Resident Table1

This didn't work. Curious for your thoughts.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Partner - Specialist II

Hi,

You can try a sxript like this:

data:
Name,
Date#(Date, 'D-M-YYYY') as Date
Inline [
Name,Date
A,2-9-2023
A,30-8-2023
A,25-1-2023
B,1-1-2023
B,5-5-2022
];

NoConcatenate
Name,
Max(Date) as Date
Where
FLG_DATE = 1
Group By
Name
;
Name,
Date,
If(Date <= Today(), 1, 0) as FLG_DATE
Resident data
;

Drop Table data;

Help users find answers! Don't forget to mark a solution that worked for you!
2 Replies
Partner - Specialist II

Hi,

You can try a sxript like this:

data:
Name,
Date#(Date, 'D-M-YYYY') as Date
Inline [
Name,Date
A,2-9-2023
A,30-8-2023
A,25-1-2023
B,1-1-2023
B,5-5-2022
];

NoConcatenate
Name,
Max(Date) as Date
Where
FLG_DATE = 1
Group By
Name
;
Name,
Date,
If(Date <= Today(), 1, 0) as FLG_DATE
Resident data
;

Drop Table data;

Help users find answers! Don't forget to mark a solution that worked for you!
Contributor
Author

Dear Aurelien,

Thanks for your reply. I copied the script and it is working. It took me some time to understand your logic but now it's clear. I was assuming that I needed the Peek/Previous but probably the lack of the DATE function it was not working. Thanks for your creative solution with the MAX function.

Regards

Tags
Community Browser