Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Number of weeks in Quarter

Good morning,

I'm tring to develop a formula for a variable tat contains the number of weeks the current quarter have.

I tried to calculate if via the difference between the number of week of the first month of the quarter and the last one. The hardest case if for Q1 :

week(makedate(year(today()),12,day(MonthEnd(makedate(year(today()),12)))))-  week(makedate(year(today()),11,1))+

week(makedate(year(today())+1,2,day(MonthEnd(makedate(year(today())+1,2)))))-week(makedate(year(today())+1,1,1))

I also tried using EndQuarter function but the problem is the same. The last week of December appears to be the week number 1, not the 52.

There is a solution for that?

Thanks

4 Replies
Specialist
Specialist

Week(31/12/2012) = 52, but ...

Week(YearEnd(Today())) = 1, even though ...

YearEnd(Today()) = '31/12/2012'

Maybe the equation is getting resolved from the outer to inner brackets somehow.

Jonathan

MVP & Luminary
MVP & Luminary

Hi,

Try this expression

=If(Week(QuarterEnd(Today())) <> 1, Week(QuarterEnd(Today())), Week(QuarterEnd(Today())-7)) - Week(QuarterStart(Today()))

Hope this helps you.

Regards,

Jagan.

MVP
MVP

Since ISO standard is used for week numbering, you might run into troubles also with the first week of the year (e.g. week of 2012-01-01 is 52 / 2011).

You could think about using US week numbering for your calculations (check Henrics post here:

http://community.qlik.com/message/258205#258205) )

or create a master calendar, with fields for Year, Quarter and Week and then count the distinct weeks in your Quarter.

Champion III
Champion III

What is your definition of the "week belongs to the Quarter"?

In most cases, it is sufficient to say that every Quarter has 13 weeks.  Other examples of definitions:

If count a week if more than half of the week belongs to the Quarter, and week starts on Sunday, count the number of Wednesdays:

=count(if(weekday(Date)=2, Date))

If you count a week is the wek start belongs to the Quarter, and the first day of the week is Monday, count the number of Mondays:

=count(if(weekday(Date)=0, Date))

Regards,

Michael