Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
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