Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I found the attached script from Henric Cronström that accomplishes what I need to do so that I calculate weeks to match pay periods but am having an issue where it is generating a week 0 which should be Week 52 and it shows the first three days of that week as 0 and the remaining 4 as week 1. (see screen shot).Henric Cronström
How do I correct this? I am attaching my modified file as well.
Jennie,
While working on the calendar with the flags it occurred to me that what you're really trying to build here is a 4-4-5 calendar where each quarter is comprised of two four week months and a five week month.
Using that here are the requirements as I understand them:
Since you chose not to the use built in routines to determine your CM, CMTD, CQTD, and CYTD type flags, I’m assuming it’s because you need to compare to the prior year’s fiscal period defined by your 52 week year (364 days). In this scenario all the 4-4-5, 4-5-4, 5-4-4 calendar implementations I’ve seen tend to fall short as they don’t really give you the ability to compare non-standard date ranges with prior periods that are also non-standard.
Let’s say you wanted to compare current YTD with prior YTD. Given this 4-4-5 calendar,
Using a TD of 11/14/2013 for the current year, we are 320 days into the year. The same TD period of 320 days into 2012 translates to 11/15/2012. 2012 was a leap year and begins on January 1st.
In order to create the date flags for compare fluctuating date ranges in this fashion I found myself using quite a bit of conditional logic to evaluate when the flags should be set. I’m not sure if there is a more elegant way to build flags for this specific purpose,
but if there are, I have yet to run across them.
I’ve included all the flags from your comments using the idea of this 4-4-5 fiscal calendar. Please take a look at the Calendar Validation sheet in the attached application to verify if these are the flags you are looking for.
I have a question for you as well. The typical 4-4-5 calendar’s I’ve seen are actually based on custom accounting periods to handle the loss of the day(s) in certain years to prevent the calendar for continuously losing ground as time goes on. In your scenario how is your customer planning on reclaiming the lost days?
Jennie,
See if this attached QVW works.
What I did was simple put a conditional in your load that checks for 0 in US_WeekNumber and replaces with 1.
Div(Date-FirstDayOfWeek1,7)+1 as US_WeekNumber
if(Div(Date-FirstDayOfWeek1,7)+1 > 0, Div(Date-FirstDayOfWeek1,7)+1, 1) as US_WeekNumber
There are probably better ways to handle this, but I think this does the trick
Matthew,
If I do this, then I get two Week 1's in 2012, it needs to be rolling from my start date
if(Div(Date-FirstDayOfWeek1,7)+1 = 0, 52, Div(Date-FirstDayOfWeek1,7)+1) as US_WeekNumber
Then I get the following results:
Week 1 of the year 2012 needs to begin on 1/8/2012; Week 52 should start on 1/1/12.
I am so close! Just need to get this to work and I am off and running in my document re-design.
Jennie
I think I'm getting closer.
Take a look at the attached. I've re-written the code to use a loop by year. The starting date is specified before the loop and the loop goes through three three iterations for each year and builds 52 weeks each. In this situation - 2014 drops off as a year - since we're basically including the first week of each year as the last week of the prior year.
Matt
Maybe this:
if(Date<6-weekday(yearstart(Date))+yearstart(Date),53,ceil(DayNumberOfYear(Date-6+weekday(yearstart(Date)))/7)) as US_Week,
To be clear. Are you actually looking for this? "Week 1 of the year 2012 needs to begin on 1/8/2012; Week 52 should start on 1/1/12"
If that's the case - then you'll be looking at looking at week 52 of 2013 starting on 12/30/2012 and week 1 of 2013 starting on 1/6/2013. Does that sound right or are you looking for weeks 1-52 to be sequential?
Here's the example showing non-sequential week numbers, Week 52 of 2012 begins 1/1/2012- Week 1 of 2012 begins 1/8/2012.
There were some other issues I believe with the script which was causing you to start on Sundays, but your first week of each year was eight days so all subsequent weeks began on Mondays.
It should now show a rolling 52 week year beginning on 1/9/2011 with 7 day weeks.
Matt