Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

From Year Month to fetch WeekEnds by Thursday In QlikSense

My Input table s below:

YearMonthCountryDeptTarget
20201IndiaSales100
20201IndiaFinance120
20202IndiaSales140
20202IndiaFinance160
20203IndiaSales180
20203IndiaFinance200
20204IndiaSales220
20204IndiaFinance240
So OnSo OnSo OnSo OnSo 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

YearMonthWeekEndByThursdayOfRespectiveMonthsCountryDeptTarget
2020102.01.2020IndiaSales100
2020102.01.2020IndiaFinance120
2020109.01.2020IndiaSales100
2020109.01.2020IndiaFinance120
2020116.01.2020IndiaSales100
2020116.01.2020IndiaFinance120
2020123.01.2020IndiaSales100
2020123.01.2020IndiaFinance120
2020130.01.2020IndiaSales100
2020130.01.2020IndiaFinance120
2020206.02.2020IndiaSales140
2020206.02.2020IndiaFinance160
2020213.02.2020IndiaSales140
2020213.02.2020IndiaFinance160
2020220.02.2020IndiaSales140
2020220.02.2020IndiaFinance160
2020227.02.2020IndiaSales140
2020227.02.2020IndiaFinance160
So OnSo OnSo OnSo OnSo OnSo On

 

Kindly help.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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

View solution in original post

3 Replies
Highlighted
Partner
Partner

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

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

View solution in original post

Highlighted

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

 

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;

 

Highlighted
Partner
Partner

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

Absolutely brilliant. Thank you so much. It works.