Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

2 Replies
Anonymous
Not applicable
Author

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.

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