4 Replies Latest reply: Jan 3, 2018 4:35 PM by Anton Aks

# Dear Qlik Experts: How to get the week number of each month and the date of each Monday each month?

Hello Qlik experts,

I have a Date field from which I have created a MasterCalendar table.

However I need to create two fields which I don't know how to:

• Week number (e.g. week 1,2,3,4,5) for each month. This field would be called WeekNumberByMonth
• Get the date of each Monday from that week. This would be called WeekDate

Here is an example of week numbers that should be assigned based on real dates:

January 2018:

Week 1: 01/01/2018

Week 2: 08/01/2018

Week 3: 15/01/2018

Week 4: 22/01/2018

Week 5: 29/01/2018

February 2018:

Week 1: 05/02/2017

Week 2: 12/02/2017

Week 3: 19/02/2017

Week 4: 26/02/2017

March: 2018

Week 1: 05/03/2017

Week 2: 12/03/2017

Week 3: 19/03/2017

Week 4: 26/03/2017

Each date above is a Monday of each week inside each month.

Anyone know how I can create something like this please?

• ###### Re: Dear Qlik Experts: How to get the week number of each month and the date of each Monday each month?

Hi,

'Week '& Ceil(Day(YourDateField)/7)          as WeekNumberByMonth,

If(WeekDay(YourDateField) = 'Mon',YourDateField) as WeekDate

I hope it helps,

Luis

• ###### Re: Dear Qlik Experts: How to get the week number of each month and the date of each Monday each month?

HI Mike,

Cheers,

Luis

• ###### Re: Dear Qlik Experts: How to get the week number of each month and the date of each Monday each month?

I think, since they have created a master calendar, the week dates should be created from the master calendar MASTER_DATE, not their date field.

• ###### Re: Dear Qlik Experts: How to get the week number of each month and the date of each Monday each month?

Try this in your master calendar.

The field what you need is NumWeekInMonthGen

LET DateStart = MakeDate(2015,1); // Upload start date

Calendar:

if(Peek(MonthYearGen)=MonthYearGen and Peek([Year-WeekGen])=[Year-WeekGen], Peek([NumWeekInMonthGen]),

if(Peek(MonthYearGen)=MonthYearGen and Peek([Year-WeekGen])<>[Year-WeekGen],Peek([NumWeekInMonthGen])+1,1))

as 'NumWeekInMonthGen'

;

ID_DATE,

ID_DATE as 'Date',

Day(ID_DATE) as 'Day',

Year(ID_DATE) as 'Year',

Month(ID_DATE) as 'Month',

num(Month(ID_DATE)) as 'MonthNum',

WeekDay(ID_DATE) as 'Week Day',

Year(ID_DATE)&num(Month(ID_DATE),'00') as 'MonthYearGen',

WeekName(ID_DATE) as 'Year-WeekGen',

NetWorkDays(ID_DATE,ID_DATE) as 'Work day';

load date('\$(DateStart)' + IterNo() -1) as ID_DATE

autogenerate 1

while ('\$(DateStart)'+IterNo()-1)<today(1);

Exit Script;