topic Re: Week Start - Sunday instead of Monday in QlikView

Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 17:07:37 GMT

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.

Re: Week Start - Sunday instead of Monday

mbm — Thu, 07 Nov 2013 17:23:34 GMT

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

Re: Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 17:29:21 GMT

Matthew,

If I do this, then I get two Week 1's in 2012, it needs to be rolling from my start date

Re: Week Start - Sunday instead of Monday

Gysbert_Wassenaar — Thu, 07 Nov 2013 17:30:52 GMT

if(Div(Date-FirstDayOfWeek1,7)+1 = 0, 52, Div(Date-FirstDayOfWeek1,7)+1) as US_WeekNumber

Re: Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 17:57:30 GMT

Then I get the following results:

Re: Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 18:14:44 GMT

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

Re: Re: Week Start - Sunday instead of Monday

mbm — Thu, 07 Nov 2013 18:31:33 GMT

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

Re: Week Start - Sunday instead of Monday

Gysbert_Wassenaar — Thu, 07 Nov 2013 19:52:51 GMT

Maybe this:

if(Date<6-weekday(yearstart(Date))+yearstart(Date),53,ceil(DayNumberOfYear(Date-6+weekday(yearstart(Date)))/7)) as US_Week,

Re: Week Start - Sunday instead of Monday

mbm — Thu, 07 Nov 2013 20:13:18 GMT

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?

Re: Re: Week Start - Sunday instead of Monday

mbm — Thu, 07 Nov 2013 20:23:04 GMT

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

Re: Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 20:24:20 GMT

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.

Re: Week Start - Sunday instead of Monday

mbm — Thu, 07 Nov 2013 20:40:57 GMT

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.

Re: Week Start - Sunday instead of Monday

jenmclean — Thu, 07 Nov 2013 20:56:25 GMT

Matt,

Testing it out...will let you know.

Jennie

Re: Week Start - Sunday instead of Monday

hic — Fri, 08 Nov 2013 10:07:51 GMT

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 ...

Re: Re: Week Start - Sunday instead of Monday

jenmclean — Wed, 13 Nov 2013 18:19:15 GMT

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

Re: Week Start - Sunday instead of Monday

jenmclean — Wed, 13 Nov 2013 19:54:37 GMT

Matt,

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

Jennie

Re: Week Start - Sunday instead of Monday

mbm — Wed, 13 Nov 2013 20:02:02 GMT

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,

Re: Week Start - Sunday instead of Monday

jenmclean — Wed, 13 Nov 2013 20:04:18 GMT

Thanks!

Re: Re: Re: Week Start - Sunday instead of Monday

mbm — Fri, 15 Nov 2013 04:27:01 GMT

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?

Re: Re: Re: Re: Week Start - Sunday instead of Monday

jenmclean — Wed, 20 Nov 2013 17:46:11 GMT

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