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

[mwallman]

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?

[luismadriz]

Hi,

Please add this to the load of your MasterCalendar.

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

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

I hope it helps,

Luis

[luismadriz]

HI Mike,

Please let me know if this helped to close this thread,

Cheers,

Luis

[johnca]

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.

[antonaks]

Try this in your master calendar.

The field what you need is NumWeekInMonthGen

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

Calendar:

LOAD *,

       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'

   ;

LOAD

    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;