10 Replies Latest reply: May 2, 2012 10:20 AM by Karthikeyan B RSS

    Start weeks on Sundays

    Gerhard Laubscher

      Hi there,

       

      There are a few discussions on this topic, but I was not yet able to apply the proposed solutions to my application.

       

      I pull data from different extracts where our dates are in this formatL DD-MMM-YY.

       

      So I usually start with this:

      DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date]

       

      Then I do the following to get the week:

      if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01] 27/06-03/07' as [Effective Week]

           The company I do these reports for start their financial year in July, so this is seen as their First Week of the year.

       

      However, their weeks start on Sundays, not Mondays. So their actial first week is 26/06-02/07 (The 26th being a Sunday).

       

      How do I get the weeks in QV to start on Sundays? So I need week 21 to be 26/06 to 02/07, not 27/06 to 03/07.

       

      Please assist.

       

      Thank you,

       

      Gerhard

       

       

        • Start weeks on Sundays

          Hi,

          By default qlikview take start week on monday,by using weekstart function u can shift weekstart date on sunday

           

          ex Weekstart('2011-06-27',0,-1)

           

           

          Thanks

          VIvek

            • Start weeks on Sundays
              Gerhard Laubscher

              Hi Vivek,

               

              I have tried this but cannot get it to work. Would you mind putting this in context with the way my load statements looks now:

               

              DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

                     if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01] 26/06-02/07' as [Effective Week],

               

              At the moment week 21 starts on Monday 27 July. I need week 21 to start on Sunday 26 July.

                • Start weeks on Sundays

                  Hi,

                  DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

                  if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01]' &weekstart(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1)&'-'&weekend(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1)) as [Effective Week],

                   

                  I hope this will help

                   

                  Thanks

                  Vivek

                    • Re: Start weeks on Sundays
                      Stefan Wühl

                      I think there is a potential issue in using the week = 21 comparison:

                       

                      Doing so, I believe this will not assign the correct Effective Week value to Sunday, 26 June, because this Effective Date is in week 25 (I assume we are talking about year 2011, but anyway, it will never be week 21).

                       

                      [edit: I think I missed my point here: Using the week function, you will not get the same week returned for Sunday 26, June and the following days to July 2nd, so you won't assign the same Effective Week to Sunday, so you are not getting what you intend to get]

                       

                      Then, if you are not doing this for one special year only, I would be cautious to compare to a fix week number, this might shift +-1 over years.

                       

                      Regards,

                      Stefan

                       

                       

                        • Start weeks on Sundays
                          Gerhard Laubscher

                          Hi Stefan,

                           

                          Yes it's week 25 sorry. But you are correct and this did cross my mind - soon it will be 2012 and then this solution won't work anyway..

                           

                          Do you ahve any suggestions?

                           

                          My starting point for many fields will just be one date. In this case the field is [Effective Date] in the DD-MMM-YY format. From these fields I must create day names and months (which are easy), and weeks.

                           

                          This will for instance be the week in which a transaction happened or an account was opened or an application was received, etc. I always have to create a week based on the day, because the clients want to see results weekly, starting on Sundays.

                           

                          I appreciate the input.

                           

                          Regards,

                           

                          Gerhard

                  • Start weeks on Sundays
                    Gerhard Laubscher

                    Okay I figured it out (I think).

                     

                    I am now adding the following:

                    Date(weekstart( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))-1)&' - '& Date(weekend( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))-1) as Eff_Week_Start,

                     

                    Then I use Eff_Week_Start as dimension to split all my tables. This way it will report on weeks from Sunday to Saturday, even though they are now no longer numbered from 1 to 52.

                     

                    Thanks for both of your inputs, they helped a lot.

                     

                    G

                      • Start weeks on Sundays
                        Stefan Wühl

                        That's probably a way to do this (though I wouldn't call it Week_Start if I would add also the weekend date to the string, but that's only a side note).

                         

                        If you want to assign a week number, you could probably just do a

                        week( weekend(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1) ) as WeekNumber

                         

                        This week number may not be correct in a certain calendar system (e.g. US) defining certain rules when to start week 1 in a year, though you might want to check this.

                      • Start weeks on Sundays
                        jagan mohan rao appala

                        Hi,

                         

                        Try this to set Start day of week to Sun

                         

                        Change this in script Main tab.

                         

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

                         

                        Regards,

                        Jagan.

                        • Re: Start weeks on Sundays

                          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

                           

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

                           

                          Try using WEEKNUM(<Date>) in Excel for both these dates.

                          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?