14 Replies Latest reply: Sep 28, 2014 10:11 AM by Lavanya Ramaswamy RSS

    Week Start - Sunday instead of Monday

      Is there any option in QlikView to set the week start globally to desired Day instead of default Monday.

      I have tried using -1 in WeekStart but there are lot of problems.

       

      Please check these out

      LET vCYDate = Date(Today());

      LET vCYWeekStart = WeekStart(vCYDate,0,-1);

      LET vCYWeekNo = Week(vCYWeekStart);

       

      LET vPYWeekDay = MakeWeekDate(Year(vCYDate)-1,vCYWeekNo, vCYWeekDay);

      LET vPYWeekStart = WeekStart(vPYWeekDay,0,-1);

      LET vPYWeekNo = Week(vPYWeekStart);

       

      The main thing is I want to get the same week details for 2 given years. Try for Year 2012 and 2011 in the way I have written the code. Except first week of Jan, the result is giving the correct result. Is there a way to overcome this?

       

      Try using WEEKNUM(<Date>) in Excel for both these dates (Jan 1st 2012 and Jan 1st 2011) as well in QlikView.

       

      Actually the WeekStart shows Sunday but the Week number of that date is showing 1 week less than the correct week # as the vCYWeekStart / vPYWeekStart is considered as the last day of the previous week in QlikView.

       

      How to globally set in QV to make Sunday as the start of the week and the rest of the date calculations follow it?

        • Week Start - Sunday instead of Monday
          Sunil Chauhan

          at the start of edit script  while defineing varibles

           

          you can change the order of

           

           

          SET ThousandSep=',';

          SET DecimalSep='.';

          SET MoneyThousandSep=',';

          SET MoneyDecimalSep='.';

          SET MoneyFormat='$#,##0.00;($#,##0.00)';

          SET TimeFormat='h:mm:ss TT';

          SET DateFormat='M/D/YYYY';

          SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

          SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

           

           

          instead of SETDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

           

          and save application

           

          hope this helps

            • Re: Week Start - Sunday instead of Monday

              SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

              is similar to SET DayNames='q;w;e;r;t;y;u';

               

              It is just to customize the language and not the real start of the week

              I tried this and it shows April 30th 2012 as Sunday though it is Monday

                • Re: Week Start - Sunday instead of Monday
                  Martin Pohl

                  Not very nice but:

                   

                   

                  Just added one day for your date.

                  So in your case:

                   

                  instead of week for 2012/04/29 is 17.

                  Regards

                    • Re: Week Start - Sunday instead of Monday

                      Atlast I got fedup with QV's inbuild date functions and wrote a VBScript to get the previous year's week date

                       

                      Function GetPYWeekDate(dDate)

                           ctYear = Datepart("yyyy",dDate)

                           ctDayofYear = Datepart("y",dDate)

                       

                           cyFirstDay =  Datepart("w",datevalue("1/1/" & ctYear))

                           pyFirstDay =  Datepart("w",datevalue("1/1/" & ctYear - 1))

                       

                           GetPYWeekDate = dateadd("d", - (pyFirstDay - cyFirstDay) + ctDayofYear - 1, datevalue("1/1/" & ctYear - 1)) 

                      End Function

                       

                      If anyone needs this, use it

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

                          It's easier if you do it in the script instead of in VBS. Leave the DayNames variable as it is and use

                           

                          Dual(Subfield('$(DayNames)',';',WeekDay(Date)+1),Mod(Date-1,7)) as MyWeekDay

                           

                          HIC

                            • Re: Week Start - Sunday instead of Monday

                              Hi Henric,

                               

                              Thanks for your input. It is returning 1 day more than the QV's normal weekday

                               

                              Today() : 5/8/2012 (Tuesday)

                              WeekDay:      Num(WeekDay(Today()),'#') : 1

                              MyWeekDay: Num(Dual(Subfield('$(DayNames)',';',WeekDay(Today())+1),Mod(Today()-1,7)),'#') : 2

                               

                              WeekStart(Today()): 5/7/2012

                              WeekStart(Today(),0,-1): 5/6/2012

                               

                              If its Tuesday, it should return either 2(Weekday) / 3(MyWeekDay), but it is returning 1 / 2. Does this mean that Weekday starts with zero?

                               

                              My function will return the foll

                              GetPYWeekDate(Today()) = 5/3/2011

                              How to achieve the above using only QV script instead of VBS?

                              We can easily subtract a day to get the week's start day as Sunday even without using these many conversions, but Week function will not return the correct Week # for the StartDate of the Week

                               

                              Is there something like AddDays similar to AddMonths & AddYears? I think that may resolve this issue. I don't want to use "+ <days>" to add days

                               

                              My basic requirement is I want to set the start of the week to be Sunday instead of Monday permanently, and not temporary (still I haven't got the answer).

                              If that is the case, then only the current year's week number and previous year's week number are tallying, else it is not.

                               

                              1/1/2012 - Weekstart should be 1 instead of 7 (1st week) - in QV, this is the last week's last day of previous year

                              and 1/1/2011 - Weekstart should be 7 instead of 6 (prev year's last week) - So 1st week of year 2011 starts on 2nd Jan and not on 3rd

                               

                              If the above condition satisfies, then only we can get the answer for proper Week over Year value

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

                                  Both the built-in WeekDay and my function start at 0 and go to 6. With this definition WeekDay for today should be 1 (second day of week) and my function should be 2 (third day of week). You can check this yourself by formatting a listbox with WeekDay as a number (Properties - Number).

                                   

                                  But for my function you can easily change this if you want an other value: The Dual function takes two parameters - the second defines the number. So, just change Mod(Date-1,7) to Mod(Date-1,7)+1. It will still work using Sunday as the first day of the week.

                                   

                                  The function WeekStart does not change, so it will still return Monday as the first day of the week. (ISO 8601; http://en.wikipedia.org/wiki/ISO_week_date)

                                   

                                  HIC

                                    • Re: Week Start - Sunday instead of Monday

                                      =MakeDate(Year(vCYStartDate)-1,1,1)

                                      - (

                                          Dual(Subfield('$(DayNames)',';',WeekDay(WeekDay(MakeDate(Year(vCYStartDate)-1,1,1)))+1),

                                                                                              Mod(WeekDay(MakeDate(Year(vCYStartDate)-1,1,1))-1,7))

                                          -

                                          Dual(Subfield('$(DayNames)',';',WeekDay(WeekDay(MakeDate(Year(vCYStartDate),1,1)))+1),

                                                                                              Mod(WeekDay(MakeDate(Year(vCYStartDate),1,1))-1,7))

                                        ) 

                                      + Dual(DayNumberOfYear(vCYStartDate)) - 1

                                       

                                       

                                      Is this the way to achieve previous year's weekday using your MyWeekDay?