Solved: Re: Re: Week Start - Sunday instead of Monday - Qlik Community

jenmclean · ‎2013-11-07

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.

mbm · ‎2013-11-14

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?

View solution in original post

mbm · ‎2013-11-07

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 · ‎2013-11-07

Matthew,

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

Gysbert_Wassenaar · ‎2013-11-07

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

talk is cheap, supply exceeds demand

jenmclean · ‎2013-11-07

Then I get the following results:

jenmclean · ‎2013-11-07

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 · ‎2013-11-07

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

Gysbert_Wassenaar · ‎2013-11-07

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 · ‎2013-11-07

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 · ‎2013-11-07

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