To Create a Master Calendar
LET vCalendarMinDate = Num(MakeDate(2014,1,1)); //Change accordingly with your data
LET vCalendarMaxDate = Num(MakeDate(2014,8,4)); //Change accordingly with your data
Calendar:
LOAD Date,
Year(Date) as Year,
Month(Date) as Month,
Day(Date) as [Day],
Week(Date) as [Week]
//Any other calendar fields you whish to create
;
LOAD
$(vCalendarMinDate)+IterNo()-1 as Date //Shoud be the FieldName in your data
AUTOGENERATE 1
WHILE $(vCalendarMinDate)+IterNo()-1<=$(vCalendarMaxDate);
In Settings->Variable Overview create the fallowing variables
Say the maximum date in the master calendar is 04/08/2015 (DD/MM/YYYY)
vMaxDate =Num(Max(Date)) //Should evaluate to 04/08/2015 (DD/MM/YYYY) = 42220
vYear =Year($(vMaxDate))
vLastYear =Year(AddYears($(vMaxDate), -1))
vMaxWeekSameYear =Week($(vMaxDate)) //Should evaluate to 32
vMaxWeekLastYear =Week(AddYears($(vMaxDate), -1)) //Same as Above
For LYTD (Last YTD)
Sum({<Calendar.Year={$(vLastYear)}, Calendar.Week={"<=$(vMaxWeekLastYear)"}>} Value) //This should accumulate the values up to Week 32 of 2014
For YTD
Sum({<Calendar.Year={$(vYear)}, Calendar.Week={"<=$(vMaxWeekSameYear)"}>} Value) //This should accumulate the values up to Week 32 of 2015
