topic Re: Variable for Last Month Last Week Start Date in QlikView

Variable for Last Month Last Week Start Date

Mon, 13 Jul 2015 00:17:05 GMT

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

Anonymous — Mon, 13 Jul 2015 00:47:09 GMT

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 — Mon, 13 Jul 2015 00:47:51 GMT

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