Find Max Date with selected date

[karan_kn]

Need to find max date  from Shipping Date field for respective ID's <='12/01/2019'

ID Shipping Date
1 4/3/2018 3:19:00 AM
1 10/22/2018 12:46:00 AM
1 10/22/2018 12:46:00 AM
1 2/11/2019 12:37:00 AM
1 2/11/2019 12:37:00 AM
1 7/23/2019 12:41:00 AM
1 7/23/2019 12:42:00 AM
1 1/7/2020 1:21:00 AM
1 1/7/2020 1:21:00 AM
1 2/11/2020 2:58:00 AM
1 2/11/2020 2:59:00 AM
2 10/20/2017 5:54:00 AM
2 1/17/2018 4:12:00 AM
2 12/7/2019 11:37:00 PM
3 10/22/2018 12:45:00 AM
3 10/22/2018 12:46:00 AM
3 2/11/2019 12:36:00 AM
3 2/11/2019 12:37:00 AM
3 6/7/2019 7:37:00 AM
3 1/7/2020 1:21:00 AM
3 1/28/2020 4:26:00 AM

[johnca · Accepted solution]

Maybe something like this? It creates a key of ID|Shipping Date and then finds the max when grouped by ID and joins it to the initial load.

data:
NoConcatenate
Load *,
ID & '|'& [Shipping Date] as Max_Ship_Date_Key;
load * inline [
ID, Shipping Date
1, 4/3/2018 3:19:00 AM
1, 10/22/2018 12:46:00 AM
1, 10/22/2018 12:46:00 AM
1, 2/11/2019 12:37:00 AM
1, 2/11/2019 12:37:00 AM
1, 7/23/2019 12:41:00 AM
1, 7/23/2019 12:42:00 AM
1, 1/7/2020 1:21:00 AM
1, 1/7/2020 1:21:00 AM
1, 2/11/2020 2:58:00 AM
1, 2/11/2020 2:59:00 AM
2, 10/20/2017 5:54:00 AM
2, 1/17/2018 4:12:00 AM
2, 12/7/2019 11:37:00 PM
3, 10/22/2018 12:45:00 AM
3, 10/22/2018 12:46:00 AM
3, 2/11/2019 12:36:00 AM
3, 2/11/2019 12:37:00 AM
3, 6/7/2019 7:37:00 AM
3, 1/7/2020 1:21:00 AM
3, 1/28/2020 4:26:00 AM
];

data2:
Left Join(data)
load
ID & '|'& FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_Date_Key,
FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_date
Resident data
Where [Shipping Date] <= '12/1/2019'
Group by ID;

[karan_kn]

Can you help me with Set Analysis instead load statement?

[johnca]

Try this...

Aggr(Max({<[Shipping Date]={"<=$(='12/1/2019')"}>}[Shipping Date]),ID)

Not knowing your data model, I would personally make that date a variable where it can be changed by the user. If you go this route the expression would be 

Aggr(Max({<[Shipping Date]={"<=$(=vDate)"}>}[Shipping Date]),ID) where vDate is your user-input variable.

HTH,

John

[Brett_Bleess]

Did John's last post get you what you needed?  If so, do not forget to come back and properly close out your thread by using the Accept as Solution button on that post of John's that got you the info you needed, as this will give him credit and let other Members know it did work.

Here is a Design Blog on Set Analysis basics that has some additional links at the bottom and after that I am including the base URL for the Design Blog area in case you want to do some further searching, as I am sure some of those posts may help further.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Actually found a couple direct posts that may be most helpful followed by the base URL.

Regards,
Brett