Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

james_hanify
New Contributor III

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

Re: Weeks in a month

Hi James, you can try with:

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

6 Replies

Re: Weeks in a month

You can use this to create week number...

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

james_hanify
New Contributor III

Re: Weeks in a month

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

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

Re: Weeks in a month

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

Re: Weeks in a month

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

Re: Weeks in a month

Hi James, you can try with:

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

james_hanify
New Contributor III

Re: Weeks in a month

Seems to work very well, thank you very much.

Community Browser