Hi Experts,
I have the below script for Master Calendar.
Let varMinDate = NUM(MAKEDATE($(vReviewPrevYear),1,1));
Let varMaxDate = NUM($(vToday)-1);
Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load *, WeekYear & '-' & NUM(WeekNumber,'00') as YearWeek;
Load *,
Div( DECDDT - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
Year( WeekYearRefDate ) as WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), DECDDT, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;
Load *,
Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;
Load
TempDate AS DECDDT,
TempDate AS Date,
NUM(TempDate) AS NumDate,
WeekStart(TempDate, 0, $(vCal_FD) ) as WeekStart ,
Year(TempDate) As Year,
Month(TempDate) As Month,
MonthName(TempDate) As MonthYear,
IF(TempDate<Today(),1,0) as TD,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as CurMTDFlag,
// If(InMonthToDate(DECDDT, '$(vToday)',0),1,0) as CurMTDFlag,
If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as LastMTDFlag,
If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as PrevMTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I have calculated the month to date values using set analysis like
Sum({< MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} SalesAmt)
Now i have to calculate(like above expre) for the current week i.e.,6/6/15 to 12/6/15 and for the previous week.
how can i do the same set analysis expression for week to date.
please guide me
