topic Re: Find Max Date with selected date in QlikView

Find Max Date with selected date

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Find Max Date with selected date

johnca — Thu, 20 Feb 2020 15:46:02 GMT

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;

Re: Find Max Date with selected date

karan_kn — Thu, 20 Feb 2020 16:04:58 GMT

Can you help me with Set Analysis instead load statement?

Re: Find Max Date with selected date

johnca — Thu, 20 Feb 2020 16:35:57 GMT

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

Re: Find Max Date with selected date

Brett_Bleess — Wed, 25 Mar 2020 19:44:14 GMT

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