Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
mwallman
Contributor II

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?

3 Replies
kathleene
New Contributor III

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

Hello - we've created an master calendar & include similar fields and here are the calculations we use in the load script.

Floor(WeekEnd((TempDate),0,-1)) as [Week Ending Sat],

Week(Floor(WeekEnd((TempDate),0,-1))) as [Week Number],

kathleene
New Contributor III

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

After further review - this may be more what you need . . . here is a previous post

Month Week ( 1 to 4 or 5 weeks)

luismadriz
Valued Contributor

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