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?

[Anonymous]

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],

[Anonymous]

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

Month Week ( 1 to 4 or 5 weeks)

[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