Qlik Community

App Development

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
mwallman
Creator III
Creator III

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
Contributor III
Contributor III

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
Contributor III
Contributor III

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

Month Week ( 1 to 4 or 5 weeks)

luismadriz
Specialist
Specialist

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