Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT 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!