topic Re: Getting Max Date before Today() in App Development

Getting Max Date before Today()

calvintang — Fri, 05 Aug 2022 02:32:10 GMT

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

sidhiq91 — Fri, 05 Aug 2022 04:30:36 GMT

@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 — Fri, 05 Aug 2022 06:37:34 GMT

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 — Fri, 05 Aug 2022 08:49:41 GMT

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

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

calvintang — Mon, 08 Aug 2022 00:28:39 GMT

Thank you! This worked!