From Year Month to fetch WeekEnds by Thursday In QlikSense

[Ron1]

My Input table s below:

Year	Month	Country	Dept	Target
2020	1	India	Sales	100
2020	1	India	Finance	120
2020	2	India	Sales	140
2020	2	India	Finance	160
2020	3	India	Sales	180
2020	3	India	Finance	200
2020	4	India	Sales	220
2020	4	India	Finance	240
So On	So On	So On	So On	So On

I want my output as below which will having all the dates which is ending with Thursday of that given month.Remaining details will be same. 

Please see my required output as below

Year	Month	WeekEndByThursdayOfRespectiveMonths	Country	Dept	Target
2020	1	02.01.2020	India	Sales	100
2020	1	02.01.2020	India	Finance	120
2020	1	09.01.2020	India	Sales	100
2020	1	09.01.2020	India	Finance	120
2020	1	16.01.2020	India	Sales	100
2020	1	16.01.2020	India	Finance	120
2020	1	23.01.2020	India	Sales	100
2020	1	23.01.2020	India	Finance	120
2020	1	30.01.2020	India	Sales	100
2020	1	30.01.2020	India	Finance	120
2020	2	06.02.2020	India	Sales	140
2020	2	06.02.2020	India	Finance	160
2020	2	13.02.2020	India	Sales	140
2020	2	13.02.2020	India	Finance	160
2020	2	20.02.2020	India	Sales	140
2020	2	20.02.2020	India	Finance	160
2020	2	27.02.2020	India	Sales	140
2020	2	27.02.2020	India	Finance	160
So On	So On	So On	So On	So On	So On

Kindly help.

[ngioux · Accepted solution]

Hi,

You can try with the following script (replace "Your Data Table" by the name of your table):

/*Get the min and max month*/
[Tmp Min Max Months]:
LOAD
    Min(MakeDate([Year], [Month], 1)) as [Min Month],
    Max(MonthEnd(MakeDate([Year], [Month]))) as [Max Month]
RESIDENT
    [Your Data Table]
;

LET [vs.Calendar.MinDate] = Num(Peek('Min Month', 0, 'Tmp Min Max Months'));
LET [vs.Calendar.MaxDate] = Num(Peek('Max Month', 0, 'Tmp Min Max Months'));

DROP TABLE [Tmp Min Max Months];

/*Generate Calendar Table*/
[Calendar]:
LOAD
    [Date],
    Num(Month([Date])) as [Month],
    Num(Year([Date])) as [Year]
;
LOAD
    Date($(vs.Calendar.MinDate) + IterNo() - 1) as [Date]
AUTOGENERATE
    1
WHILE
    ($(vs.Calendar.MinDate) + IterNo() - 1) <= $(vs.Calendar.MaxDate)
;

/*Joining data*/
LEFT JOIN ([Your Data Table])
LOAD
    [Year], /*Key*/
    [Month], /*Key*/
    [Date]
RESIDENT
    [Calendar]
WHERE
    WeekDay([Date], 0) = 3 /*0=Monday, 1=Tuesday, 2=Wednesday, 3=Thursday, ...*/
;

DROP TABLE [Calendar];

Hope this will help.

BR,

Nicolas

[Kushal_Chawda]

T1:
LOAD
    "Year",
    "Month",
    MakeDate("Year","Month") as DateTemp,
    Country,
    Dept,
    Target
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1)
Where not wildmatch("Year",'*So*');

Left Join(T1)
Load date(Date) as Date,
     year(Date) as "Year",
     num(month(Date)) as "Month"
Where WeekDay(Date)=3;
Load MinDate+IterNo()-1 as Date
While MinDate+IterNo()-1<=MaxDate;
Load monthend(max(FieldValue('DateTemp',RecNo()))) as MaxDate,
     min(FieldValue('DateTemp',RecNo())) as MinDate
AutoGenerate FieldValueCount('DateTemp');

Drop Field DateTemp;

[Ron1]

Absolutely brilliant. Thank you so much. It works.