Week to date in calendar

[#1]

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

[ramoncova06 · Accepted solution]

in this case just tweek what Sunindia, already posted

=Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)0, 5 )<=$(=Date(vMaxSalesDate))'} >} SalesAmt)

[#3]

thanks ramon

[#4]

Hi sunindia,

can u pls help me on the thread conditional values..Pls help ....itssssssssss urg...