Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for 
Search instead for 
Did you mean: 
calvintang
Contributor III
Contributor III

Getting Max Date before Today()

Data frame:

Issue Date | Issue ID | Issue Type

2022-02-26 | 1 | Organizational 

2022-02-27 | 2 | Technical

2022-05-20 | 3 | Systems

2022-09-20 | 4 | Switchover

Ideally, I'd like to get the latest date before today(). In the scenario above, that would be 2022-05-20, instead of 2022-09-20.

May I know what is the syntax to achieve this?

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
sidhiq91
Specialist II
Specialist II

@calvintang  Please use the below expression to get the desired output.

Max({<[Issue Date]={"<=$(=Date(today(),'MM/DD/YYYY'))"}>}[Issue Date])

sidhiq91_0-1659689363250.png

 

View solution in original post

4 Replies
sidhiq91
Specialist II
Specialist II

@calvintang  Please see the script below: 

NoConcatenate
Temp:
load Date(Date#([Issue Date],'YYYY-MM-DD'),'MM/DD/YYYY') as [Issue Date],
[Issue ID],
[Issue Type]

Inline [
Issue Date | Issue ID | Issue Type

2022-02-26 | 1 | Organizational

2022-02-27 | 2 | Technical

2022-05-20 | 3 | Systems

2022-09-20 | 4 | Switchover] (delimiter is '|');

Inner Join (Temp)
Temp1:
Load Date(Max([Issue Date]),'MM/DD/YYYY') as [Issue Date];

Load
[Issue Date],
[Issue ID],
[Issue Type]
Resident Temp
where [Issue Date]<Today();

//Drop table Temp;

Exit Script;

If this resolves the issue, please like and accept it as a solution.

calvintang
Contributor III
Contributor III
Author

I am sorry, but I am unable to reload the data in that fashion - is there a different way using measure? Set analysis perhaps?

sidhiq91
Specialist II
Specialist II

@calvintang  Please use the below expression to get the desired output.

Max({<[Issue Date]={"<=$(=Date(today(),'MM/DD/YYYY'))"}>}[Issue Date])

sidhiq91_0-1659689363250.png

 

calvintang
Contributor III
Contributor III
Author

Thank you! This worked!