2 Replies Latest reply: Jul 12, 2015 9:44 PM by Sunny Talwar RSS

    Variable for Last Month Last Week Start Date

      Hello ,

       

      I am constructing a variable for LastMonth's Last WeekStart Date based on my business Calendar, in that 24th will fall under the last week of every month(i.e For example today it's July 12th, 24 th comes on friday that week will be the last week('7/20/2015') of July. Week Starting 27 will be considered as AUG).

       

      My Qlikview Application is scheduled to run every sunday. So based on every sunday's week start , I have to bring the LastMonth's Last weekStart Date. (Ex: Today's date is 7/12/2015, weekStart of Today will be 7/6/2015. So, LastMonthLastweekStart Date of 7/6/2015 should get 6/22/2015)

       

      Below constructed is my variable based on above criteria:

       

      =if(day(WeekStart(MonthEnd(addmonths(Today(),-1))))<25,

                  WeekStart(MonthEnd(addmonths(Today(),-1))),

                        date(WeekStart(MonthEnd(addmonths(Today(),-1)))-7))


       

       

      But this variable is working almost all dates but not for the dates like '5/31/2015'. If I replace Today() with '5/31/2015', it is giving the result '4/20/2015' but it should actually bring '5/18/2015'. Cause '5/31/2015' Should be considered as JUNE and it should bring MAY Month's LastWeek's Startdate, So '5/18/2015'.

       

      My Variable is not working as it should. Some one Kindly help me on this .

       

      Any help on this would be greatly appreciated.

      Thanks in Advance,

      Jaya

        • Re: Variable for Last Month Last Week Start Date
          Michael Solomovich

          Try this:

           

          =weekstart(MakeDate(

          Year(addmonths($(vDate),-1)),

          month(AddMonths($(vDate),if(day(weekstart($(vDate)))<24,-1,0))),

          24))

           

          The vDate is the variable with date I used to test this.

          The bold part should take care of the 05/31/2015 situation.

          • Re: Variable for Last Month Last Week Start Date
            Sunny Talwar

            May be this::

             

            =If(Day('05/31/2015') <> Day(MonthEnd('05/31/2015')),

            If(day(WeekStart(MonthEnd(addmonths('05/31/2015',-1)))) < 25, WeekStart(MonthEnd(addmonths('05/31/2015',-1))),

                              date(WeekStart(MonthEnd(addmonths('05/31/2015',-1)))-7)),

            If(day(WeekStart(MonthEnd(addmonths('05/31/2015' + 1,-1)))) < 25, WeekStart(MonthEnd(addmonths('05/31/2015' + 1,-1))),

                              date(WeekStart(MonthEnd(addmonths('05/31/2015' + 1,-1)))-7)))

             

            Works for 05/31/2015 and Today(), but might need testing for other dates.

             

            HTH

             

            Best,

            Sunny