Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
caltmag22
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
Specialist

@caltmag22  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
Specialist

@caltmag22  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.

caltmag22
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
Specialist

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

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

sidhiq91_0-1659689363250.png

 

caltmag22
Contributor III
Contributor III
Author

Thank you! This worked!