Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
james_hanify
Creator
Creator

Weeks in a month

Hi all,

I am really struggling with the weeks in the month, it seems to work for the most part, then fails miserably in January 16.

this is what is currently in situ:

if((week(Date) - week(monthstart(Date)) + 1)<1,1,(week(Date) - week(monthstart(Date)) + 1)) as WeekNumberInMonth,

I later found its because the Start of week month is remaining at 53 for the whole of january 2016 because 01/01/16 is week 53 so the maths goes wrong.

I've found out I can fix this by saying :

If (week(monthstart(Date)) >= week(Date)

This is the scenario:

    

Week StartWeekOfYearWeekNoMonthStartExpected Result
1st January 1653531
4th January 161532
11th January 162533

Then what i would like to do is based on the current configuration

If the above matches then use the biggest number, replace any instances by 0 and +1

Therefore

0+0+1 = 1

1+0+1 = 2

2+0+1 = 3

However, I'm not sure how you'd do this?

Many thanks for any assistance.

James

1 Solution

Accepted Solutions
rubenmarin

Hi James, you can try with:

=Ceil((Date(DateField)-MonthStart(DateField)+WeekDay(MonthStart(DateField))+1) /7)

View solution in original post

6 Replies
MK_QSL
MVP
MVP

You can use this to create week number...

Div(Date-YearStart(Date,0,1),7)+1 as WeekNumber

james_hanify
Creator
Creator
Author

Sorry I meant per month though, not weeks of the year

I've just been using Week(Date) for that.

MK_QSL
MVP
MVP

What exactly you want?

You want week number to be started by 1 from 1st January?

Or you want number of weeks by month?

james_hanify
Creator
Creator
Author

I would like

1st January to 3rd January 2016 to be week 1 and so fourth

1st Feburary to 7th Feburary 2016 to be week 1 and so fourth

1st March to 6th March 2016 to be week 1 and so fourth

please

rubenmarin

Hi James, you can try with:

=Ceil((Date(DateField)-MonthStart(DateField)+WeekDay(MonthStart(DateField))+1) /7)

james_hanify
Creator
Creator
Author

Seems to work very well, thank you very much.