Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
iamscribe
New Contributor III

Calendar starting on the 1st with partial weeks

My team is working on some allocation numbers and I haven't been able to figure out how this calendar build should work.  Each month/week grouping should start on the 1st of the month and count in 7 day (or less) increments until the end of each month.  With this information, I will be able to allocate how many people need to be available per week and per month without having to worry about roll-overs between months.

Week Groupings:

Jul 1-7Jul 8-14Jul 15-21Jul 22-28Jul 29-31
Aug 1-7Aug 8-14Aug 15-21Aug 22-28Aug 29-31
Sept 1-7Sept 8-14Sept 15-21Sept 22-28Sept 29-30
Oct 1-7Oct 8-14Oct 15-21Oct 22-28Oct 29-31

I just can't figure out how to do the extra group of leftover days.  Would it be a good place to start to group the day numbers to assign MonthName, Week1, Week2, Week3, Week4, Week5?

Thank you

1 Solution

Accepted Solutions
qliksus
Valued Contributor

Re: Calendar starting on the 1st with partial weeks

As you want to group within the month without crossover the below logic should work

LOAD Date, month(Date), ceil(day(Date)/7) as week

From table ;

2 Replies
qliksus
Valued Contributor

Re: Calendar starting on the 1st with partial weeks

As you want to group within the month without crossover the below logic should work

LOAD Date, month(Date), ceil(day(Date)/7) as week

From table ;

iamscribe
New Contributor III

Re: Calendar starting on the 1st with partial weeks

Thank you Susant,

This worked for me:

ceil(Day(Date(D))/7 as WeekNum

Community Browser