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

[Joaquin_Lazaro]

Hi PRRAJENDRAN :

Look this functions on the help file:

Weekyear()

Weekday()

Week()

Hope this helps you

Joaquín

[sunny_talwar]

Have you tried this:

Current Week

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

PreviousWeek

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

[Joaquin_Lazaro]

Hi again:

Perhaps you need this

LOAD ...

InWeek(MyDate,today(),-1) * (-1)  as %f_PreviousWeek,

InWeek(MyDate,today(),0) * (-1)  as %f_ThisWeek,

Sum({< %f_ThisWeek  = {1} } >} SalesAmt)

Sum({< %f_PreviousWeek = {1} } >} SalesAmt)

Joaquín

[#5]

Hi,

if i try this Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} SalesAmt),

it returning data from 8/6/15 to 14/6/15 which is not correct.

In my calendar, the week starts on saturday and ends on Friday.

pls help me with the expression for this corresponding weekstart date

[#6]

Hi,

Its not working correct.

In my calendar, the week starts on saturday and ends on Friday.

pls help me with the expression for this corresponding weekstart date

[#7]

PLS GIVE SOME SUGESSTIONS.............

[sunny_talwar]

WeekStart() function allows for offsetting the days

Try this in a text box and see if you get desired dates

='>=' & Date(WeekStart(vMaxSalesDate, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, -2))

If it does than try this:

=Sum({<MonthYear=, Date = {"$(='>=' & Date(WeekStart(vMaxSalesDate, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, -2)))"}>} SalesAmt)

[#9]

No,

It is going two weeks back.

pls help

looking forward to ur reply

[sunny_talwar]

Sorry, I guess its the third parameter:

='>=' & Date(WeekStart(vMaxSalesDate, 0, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, 0, -2))