topic Re: From Year Month to fetch WeekEnds by Thursday In QlikSense in App Development

From Year Month to fetch WeekEnds by Thursday In QlikSense

Ron1 — Wed, 08 Jan 2020 12:46:40 GMT

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.

Re: From Year Month to fetch WeekEnds by Thursday In QlikSense

ngioux — Wed, 08 Jan 2020 14:13:55 GMT

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

Re: From Year Month to fetch WeekEnds by Thursday In QlikSense

Kushal_Chawda — Wed, 08 Jan 2020 16:14:55 GMT

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;

Re: From Year Month to fetch WeekEnds by Thursday In QlikSense

Ron1 — Thu, 09 Jan 2020 08:55:05 GMT

Absolutely brilliant. Thank you so much. It works.