7 Replies Latest reply: Nov 11, 2014 6:42 PM by Marco Wedel RSS

    week

      hi guys,

      i have a year <<2009>>, and i want to display all the weeks in this year,

      can anybody help please!!

        • Re: week
          Ali Hijazi

          Let vMinDate= num(DayStart(makeDate(2009)))

          let vMaxDate= num(DAyStart(makeDate(2009,12,31)))

           

          tempCalendar:

          LOAD $(vMinDate) + RowNo() -1 as DateNumber

          AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

           

           

          MasterCalendar:

          LOAD DateNumber ,

          Month(date(DateNumber)) as Month,

          Year(date(DateNumber)) as Year,

          Week(Date(DateNumber)) as Week,

          'Q' & ceil(Month(Date(DateNumber))/3) as Quarter

          Resident tempCalendar;

          drop Table tempCalendar;

          • Re: week
            marc carreras

            use Week() function:

            =Week(yourdatefield)

            will return you the number of week within your year.

             

            Marc.

            • Re: week
              Anand Chouhan

              Hi,

               

              Try to load Min and max YearStart and Year End of 2009 and then create master calendar for week fields.

               

              Raw:

              Load * Inline

              [ Year

              2009 ];

               

              Dates:

              LOAD

              NUM(YearStart(MakeDate(2009))) as YearStartDate,

              NUM(YearEnd(MakeDate(2009))) as YearEndDate

              Resident Raw;

               

              Let vMinDate = peek('YearStartDate',0,'Dates');

              let vMaxDate = peek('YearEndDate',0,'Dates');

               

              tempCal:

              LOAD $(vMinDate) + RowNo() -1 as TempDate

              AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

               

              MasterCalendar:

              LOAD

              TempDate ,

              Date(TempDate) as  Date,

              Month(date(TempDate)) as Month,

              Year(date(TempDate)) as Year,

              Week(Date(TempDate)) as Week

              Resident tempCal;

               

              DROP Table tempCal;

              DROP Table Dates;

              DROP Table Raw;

               

              And fields you get like

              Weeks.png

               

              Regards

              Anand

                • Re: week
                  Ali Hijazi

                  this is a more elegant format of my answer

                    • Re: week
                      Anand Chouhan

                      Hi Youssuf,

                       

                      Yes because your requirement is you have only Year field which has only 2009 means we have requires weeks of 2009 from 01/01/2009 to 31/12/2009. But using only Week(Datefield) is worked when you have date field right so in my post i requires Year start and Year End of the 2009 as you can see the load script for this.

                       

                      Regards

                      Anand

                      • Re: Re: week
                        Marco Wedel

                        Hi,

                         

                        maybe this version helps also:

                         

                        QlikCommunity_Thread_141577_Pic2.JPG.jpg

                         

                        LET vYear = 2009;
                        
                        MasterCalendar:
                        LOAD *,
                            Month(Date) as Month,
                            Year(Date) as Year,
                            Week(Date) as Week;
                        LOAD Date(MakeDate($(vYear))+IterNo()-1) as Date
                        AutoGenerate 1
                        While Year(MakeDate($(vYear))+IterNo()-1)=$(vYear);
                        

                         

                        regards

                         

                        Marco

                    • Re: week
                      Marco Wedel

                      Hi,

                       

                      just one more:

                       

                      LET vYear = 2009;
                      
                      tabWeekCalendar:
                      LOAD *,
                          Week(WeekStart) as Week,
                          WeekName(WeekStart) as WeekName,
                          WeekYear(WeekStart) as WeekYear;
                      LOAD MakeWeekDate($(vYear),IterNo()) as WeekStart
                      AutoGenerate 1
                      While WeekYear(MakeWeekDate($(vYear),IterNo()))<=$(vYear);
                      

                       

                      QlikCommunity_Thread_141577_Pic1.JPG.jpg

                       

                      hope this helps

                       

                      regards

                       

                      Marco