Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mwallman
Not applicable

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?

4 Replies
luismadriz
Not applicable

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

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
Not applicable

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

HI Mike,

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

Cheers,

Luis

johncaqc
Not applicable

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.

antonaks
Not applicable

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:

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;