Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
in this case just tweek what Sunindia, already posted
=Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)0, 5 )<=$(=Date(vMaxSalesDate))'} >} SalesAmt)
Hi PRRAJENDRAN :
Look this functions on the help file:
Weekyear()
Weekday()
Week()
Hope this helps you
Joaquín
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)
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
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
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
PLS GIVE SOME SUGESSTIONS.............
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)
No,
It is going two weeks back.
pls help
looking forward to ur reply
Sorry, I guess its the third parameter:
='>=' & Date(WeekStart(vMaxSalesDate, 0, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, 0, -2))