Week Start - Sunday instead of Monday

[jenmclean]

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.

[jenmclean]

Matthew,

You are correct:

Week 52 of 2013 starting on 12/30/2012 and week 1 of 2013 starting on 1/6/2013.

I need to report on Pay Dates (2 weeks) and Pay Periods (13 Pay Periods Per Year) and not Calendar weeks.

Week starts on Sunday and ends on Saturday as they run a 6 day work week. This is a service based company.

[mbm]

Ok - the QVW I posted at 3:23 is set up the way you describe. Please take a look and tell me what you think.

[jenmclean]

Matt,

Testing it out...will let you know.

Jennie

[hic]

The example in the blog post assumes broken weeks over New Year, which is not what you want...

The following code will generate numbers where Jan 4th always belongs to week one, Sunday is the first day of the week, and there are no partial weeks. I am sure that there are other ways to do it, too.

HIC

Load *,

  Div(Date - SundayWeekYearStart, 7) +1 as SundayWeekNumber    ;

Load *,

  If(WeekStart(Date,0,-1) < MakeDate(Year(Date)-1,12,29), WeekStart(YearStart(Date,-1)+3,0,-1),

  If(WeekStart(Date,0,-1) >= MakeDate(Year(Date),12,29), WeekStart(YearStart(Date,+1)+3,0,-1),

  WeekStart(YearStart(Date)+3,0,-1)

  )) as SundayWeekYearStart  ;

Load Date ...

[jenmclean]

Getting back to this calendar issue, I think I have the basic calendar down and now I need to generate additional calendar flags. I am attaching my latest version of this which includes comment code from the old caledar that I am trying to redevelop.

I also need to report by Pay Period which I have as a commented line as well.

This calendar programming is not my forte! Any and all help is much appreciated. I work on this project on Wednesday and Thursdays and would love to have this done this week.

Thanks in advance!

Jennie

[jenmclean]

Matt,

I posted a new file if you have a chance to look it.

Jennie

[mbm]

Yes - I've got it an will post an update later this evening. It's definitely getting weird!

Using the model of having week 52 offset and technically the first week of the year, I have the pay periods set up to show weeks 52, 1, 2 and 3 as PP1. The other flags will take a bit of work,

[jenmclean]

Thanks!

[mbm · Accepted solution]

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: 

• Create a calendar using Sunday as the week start.
• Week 52 is the first week of the fiscal year, followed by week1, 2, 3 …
• Begin calendar from specified date as week 1
• 52 weeks per year
• 26 pay period per year
• 13 weeks per quarter (4-4-5)
• Flags built to compare equivalent “Fiscal” periods, not calendar periods.

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,  

• “Fiscal” 2013 begins 12/30/2012
• “Fiscal” 2012 begins 1/1/2012   
  

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 1^st.

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?

[jenmclean]

Matt,

This calendar is working correctly for my application but i need a few additional items.

Along with Period reporting, I need to compare current week period to the same week period from last year. The previous code had mappings for:

Current Period

Current Period Week

Last Year Pay Period (this is the one I really need)

Basically, I have the Gross Revenue from the current week and then I need to show Gross Revenue from the same week from the Previous Year and then I calculate goals and averages (15%).

Again, thank you so much for your help, I am so close now I can taste it!

I am attaching my file so that you can see how I am applying it if needed.

Thanks!

Jennie