14 Replies Latest reply: Aug 29, 2012 4:52 PM by Christine Hill RSS

    Loop through dates

    Alex Peasley

      Okay, so I've created these variables based on max date. How would I change the vShift variable to loop through all of my dates? I want to set my dates back for leap years and compare this years day, to last years day.


      LET vMaxDate1 = Date('$(vMaxDate)');

      LET vMaxDate_Num = num('$(vMaxDate)');

      LET vDayOfWeek_Max = weekday('$(vMaxDate)');

      LET vDayOfWeek_Max_Num = num(weekday('$(vMaxDate)'));



      LET vShift =

      IF (

                (mod(year('$(vMaxDate)'), 4) = 0


                ($(vMaxDate_Num) >= num(makedate(year('$(vMaxDate)'), 2, 29))))



                (mod(year('$(vMaxDate)'), 4) = 1


                ($(vMaxDate_Num) < num(makedate(year('$(vMaxDate)'), 3, 1))))

                , 2, 1);


      LET vMinDate = addmonths('$(vMaxDate)', -12, -1);

      LET vMinDate_Num = num('$(vMinDate)');

      LET vDayOfWeek_Min = weekday('$(vMinDate)');

      LET vDayOfWeek_Min_Num = num(weekday('$(vMinDate)'));



      LET vMinDate_Num_Adjusted = $(vMinDate_Num) + $(vShift);

      LET vMinDate_Adjusted = date('$(vMinDate_Num_Adjusted)', 'MM/DD/YYYY');

      LET vDayOfWeek_Min_Adjusted = weekday('$(vMinDate_Adjusted)');

      LET vDayOfWeek_Min_Num_Adjusted = num(weekday('$(vMinDate_Adjusted)'));

        • Loop through dates
          Van Huynh

          It appears that your vMinDate_Num_Adjusted calculation always* gives you the date 364 days ago. Why not use


          LET vMinDate_Num_Adjusted = Date(vMaxDate - 364);




          * Avoid any leap year determination in your scripts since leap years are not always every fourth year. http://en.wikipedia.org/wiki/Leap_year#Algorithm

          • Loop through dates
            Henric Cronström

            All QlikView functions use the correct Gregorian leap year algorithm. So, just as vhuynh says - avoid any leap year calculation. You do not need any.


            If you want to set the date back one year, use the AddYears function:

            Let vMinDate_Num_Adjusted = AddYears(vMaxDate, -1);


            If you want to compare this year's day n with last years day n, then last year's day n is found through:

            Let vMinDate_Num_Adjusted = vMaxDate - MakeDate(Year(vMaxDate)) + MakeDate(Year(vMaxDate)-1) ;