Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

Week Start - Sunday instead of Monday

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.

Weeks.png

Tags (1)
1 Solution

Accepted Solutions
mbm
New Contributor III

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

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

29 Replies
mbm
New Contributor III

Re: Week Start - Sunday instead of Monday

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

jenmclean
New Contributor II

Re: Week Start - Sunday instead of Monday

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

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


talk is cheap, supply exceeds demand
jenmclean
New Contributor II

Re: Week Start - Sunday instead of Monday

Then I get the following results:

Weeks2.png

jenmclean
New Contributor II

Re: Week Start - Sunday instead of Monday

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

mbm
New Contributor III

Re: Re: Week Start - Sunday instead of Monday

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

Maybe this:

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


talk is cheap, supply exceeds demand
mbm
New Contributor III

Re: Week Start - Sunday instead of Monday

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?

mbm
New Contributor III

Re: Re: Week Start - Sunday instead of Monday

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

Community Browser