Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get the week no for the month?

Hai,

I want to get the Week no for the month not the year. Please refer the below example and do the needful.

Year Month Week

2009 Jan 1
2009 Jan 2
2009 Jan 3
2009 Jan 4
2009 Feb 1
2009 Feb 2
2009 Feb 3
2009 Feb 4
2009 Mar 1
2009 Mar 2
2009 Mar 3
2009 Mar 4


Thanks in advance...

23 Replies
Not applicable
Author

Hi, I did same trouble.

example..

Load XYMD,

Year(XYMD),

Month(XYMD),

Num( Week(XYMD) ) - Week( MonthStart(XYMD) ) + 1 as Weeks,

.............

Or Edit Expression,

=Num(XWeekNum)-Week( MonthStart(XYMD) ) + 1

prieper
Master II
Master II

What would you defined as first week in the month?
The week starting with the 1st of the month, the first Sunday in a month? or with a similar formula as used for the first week of the year (count only, if there are more than 4 days falling into the week)?

Peter

johnw
MVP
MVP

As Peter's question suggests, this is a complicated issue that depends entirely on what you MEAN by the week of the month.

To start with, what exactly do you want displayed for the following example dates?

December 28, 2008 -> ?
December 29, 2008 -> ?
January 1, 2009 -> ?
January 3, 2009 -> ?
January 4, 2009 -> ?
January 5, 2009 -> ?
March 29, 2009 -> ?
March 30, 2009 -> ?
April 1, 2009 -> ?
April 4, 2009 -> ?
April 5, 2009 -> ?

As for sky777a's expression, it would return these answers:

December 28, 2008 -> 4
December 29, 2008 -> -47
January 1, 2009 -> 1
January 3, 2009 -> 1
January 4, 2009 -> 1
January 5, 2009 -> 2
March 29, 2009 -> 5
March 30, 2009 -> 6
April 1, 2009 -> 1
April 4, 2009 -> 1
April 5, 2009 -> 1

Notice the -47 result. That demonstrates one of the potential pitfalls for this question.

Also, do you want ONLY the week number as a separate field like this? Or did you want to have the year and month as part of the field, like 2009 Jan 1. I was unclear from how the question was written.

Not applicable
Author

Hai,

Actually I want to display the weeks in the following way.

For Example

Day 1st to 7th as 1,

Day 8th to 14th as 2,

Day 15th to 21st as 3,

Day 22nd to 28th as 4,

Day 29th to MonthEnd as 5

I written an expression like

set vweek= if( day([inv-date]) >=1 and day([inv-date]) <= 7,1,

if( day([inv-date]) >=8 and day([inv-date]) <= 14,2,

if( day([inv-date]) >=15 and day([inv-date]) <= 21 ,3,

if( day([inv-date]) >=22 and day([inv-date]) <= 28 ,4,5)))) ;

This solved my problem…

Thanks for all of your support…

Not applicable
Author

that if's gonna fail in 2 weeks...

Not applicable
Author

ok...instead of just criticizing, I've decided to give this a try.

I've attached a QV with a vbscript (that I copied from some site) which you can use with your load script. It may need some tweaking to work with your regional settings and what you consider the first day of the week (sunday or monday) but with my settings, it works.

johnw
MVP
MVP


dragonauta wrote:
that if's gonna fail in 2 weeks... <div></div>


Perhaps I'm missing something, but I see no way for that expression to fail. You can't have more than 31 days in a month, so it should always return a week number. That said, you could get the same result using the much simpler:

ceil(day(inv-date)/7)

Not applicable
Author


John Witherspoon wrote:
<pre>
dragonauta wrote:
that if's gonna fail in 2 weeks... <div></div>


Perhaps I'm missing something, but I see no way for that expression to fail. You can't have more than 31 days in a month, so it should always return a week number. That said, you could get the same result using the much simpler:

ceil(day(inv-date)/7)

You can't see how it would fail? Then I'm not understanding something. Indifferent Take this first part:


if( day([inv-date]) >=1 and day([inv-date]) <= 7,1,


and apply it to 5-Oct-2009. It would return that date being in the 1st week of October, when it would be the 1st day of the 2nd week.

johnw
MVP
MVP

Day(5-Oct-2009) would return 5. 5 >=1 and <=7, so the expression would return 1. That is what was requested:

"Day 1st to 7th as 1,
Day 8th to 14th as 2,
Day 15th to 21st as 3,
Day 22nd to 28th as 4,
Day 29th to MonthEnd as 5"

I wouldn't normally report weeks this way - I'd normally be using Sunday through Saturday (USA). But the given expression will return the requested result.