Number of weeks in Quarter

[#1]

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

[Anonymous]

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

[jagan]

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.

[swuehl]

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.

[Anonymous]

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