29 Replies Latest reply: Dec 27, 2013 10:07 AM by Jennie Elliott RSS

    Week Start - Sunday instead of Monday

    Jennie Elliott

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


      How do I correct this? I am attaching my modified file as well.


        • Re: Week Start - Sunday instead of Monday
          Matthew Bulmer


          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
            Gysbert Wassenaar

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

            • Re: Week Start - Sunday instead of Monday
              Henric Cronström

              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.




              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),


                )) as SundayWeekYearStart  ;

              Load Date ...

                • Re: Re: Week Start - Sunday instead of Monday
                  Jennie Elliott

                  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!



                    • Re: Re: Re: Week Start - Sunday instead of Monday
                      Matthew Bulmer



                      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?

                  • Re: Week Start - Sunday instead of Monday
                    Matthew Bulmer



                    Unfortunately the Calendar object within QlikView is stuck being ordered from Monday - no way to change that, so you ARE seeing 1/1/2011 show up on a Saturday correctly. There are workarounds to this, but it takes a bit more to get it working than a regular Calendar object does. I've attached an example showing a custom calendar object which starts from Sunday. One thing to notice with the custom calendar is that you won't see the day numbers for the months before and after the month you're viewing on the calendar grid. Look at how the actual calendar object shows you the Dec 10 dates and Feb 11 Dates.




                    custom calendar doesn't show those - BUT it does have the Day of the week nicely displayed so there is no confusion:




                    Full credit to johnw for creating the custom calendar object which I pulled from this thread: http://community.qlik.com/thread/12623


                    I modified his example slightly to use native triggers and added a few new triggers to set the default year and month of the calendar popup if the date value is changed outside the calendar. That way whenever you open the calendar popup you'll see the current selected date highlighted.


                    The example requires a few steps to work into your application.

                    1. Use the latest script to generate the actual calendar. I had to add a field for ActualYear, since the Year field in your calendar is a Fiscal year.
                    2. Create the Data island to conditionally show and hide the calendar pop-up and another island which is used for the calendar popup view. (These are both in a new tab in your load script, you would just need to copy them into your app)
                    3. Create Document triggers on the new CWeekday and CWeekstart fields. These triggers set the Date on the actual calendar to match the Date value from the data island (CDate) based on your selection in the calendar pop-up. Those triggers also clear the Date island fields.
                      1. Navigate to Settings>Document Properties>Triggers Tab>Field Event Triggers
                      2. Copy the OnSelect triggers for the CWeekday, CWeekstart
                    4. Create Trigger on the "Show Calendar?" field to assign the Year and Month values in the Date Island so the popup defaults to the current month and year you have selected in the Actual Calendar
                      1. Navigate to Settings>Document Properties>Triggers Tab>Field Event Triggers
                      2. Copy the OnSelect triggers for the "Show Calendar?" fields.
                    5. The Month and Year in the data island table (CMonth and CYear) need to be set to Always One Selected value.
                      1. Make sure a value is selected for month and year in the calendar popup.
                      2. Right click the month and year fields.
                      3. Navigate to Properties>Presentation Tab.
                      4. Select CMonth or CYear  in the fields list
                      5. Click the checkbox "Always one selected value"
                    6. I've set up the data island for the calendar object to contain dates from 1/1/1995 through 1/1/2025. If you want to change the available date range for the calendar object, make sure that you begin with a Sunday, otherwise your calendar popup won't be ordered properly.