Identify the latest month+Year from previous Year and match with certain date

[Theo_Westseit]

Hello Guys,

thanks to help of you guys (esp. User WildmoserGeorg), im able now to Identify the latest month+Year and match it with certain date. -> https://community.qlik.com/t5/New-to-Qlik-Sense/Identify-the-latest-month-Year-and-match-with-cerati...

So i have the following table :

Month.Year

12.2022

01.2023

11.2022

02.2023

I want do i identify the latest / youngest period+Year, which would be 02.2023.
Based on the identified/youngest period+Year, i want to identify the latest date from 02.2023 from a different table and return a value.

The table nr.2 looks like this:

Date (DD/MM/YYYY)	Value
02.03.2023	1,5
01.03.2023	1,9
23.12.2022	1,8
28.02.2023	1,4
27.02.2023	1,8

The latest date from 02.2023 is 28.02.2023, so the value to be returned should be 1,4.
Thanks to the script (see link), im now getting the value 1,4.
Next month, the value will change since the max month+Year will change from 02.2023 to 03.2023 (and 31.03.2023/Value 2,5 will be added to table 2).

Now i want the script to be extended with the latest date from the previous year (23.12.2022/Value 1,8) from table 2. But i want it to be stored in a separate table -> maxc (not table maxb, see link). I just want the previous year, so there should be only one data, which should be erased as soon as the year changes.

Can anyone help me out?

[WildmoserGeorg · Accepted solution]

Hi, try this:

Script:

a:
load * inline [
MonthYear
12.2022
01.2023
11.2022
02.2023
];

b:
load * inline [
Date,Value
02.03.2023,1.5
01.03.2023,1.9
28.02.2023,1.4
27.02.2023,1.8
23.12.2022,1.8
];

maxa:
load
date(max(date#(MonthYear,'MM.YYYY'))) as MaxaMonthStart,
monthend(date(max(date#(MonthYear,'MM.YYYY')))) as MaxaMonthEnd
resident a;

Inner Join IntervalMatch ( Date )
LOAD MaxaMonthStart, MaxaMonthEnd
Resident maxa;

maxb:
noconcatenate load
date(max(Date)) as maxdate
resident maxa;

left join (maxb) load Date as maxdate, Value as Valueb resident b;

drop table maxa;

maxapj:
load
date(max(date#(MonthYear,'MM.YYYY'))) as MaxpjMonthStart,
monthend(date(max(date#(MonthYear,'MM.YYYY')))) as MaxpjMonthEnd;

load distinct *
resident a WHERE year(date#(MonthYear,'MM.YYYY')) = Year(YearStart(today())-1);
;

Inner Join IntervalMatch ( Date )
LOAD MaxpjMonthStart, MaxpjMonthEnd
Resident maxapj;

maxc:
noconcatenate load
date(max(Date)) as maxdatec
resident maxapj;

left join (maxc) load Date as maxdatec, Value as valuec resident b;

drop table maxapj;

[Theo_Westseit]

Thank you so much!!