Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
can any body give the script for master calender generation
Hi Master,
Try this below script,
LET varMinDate = Num(Peek ('OrderDate',0,'Orders'));
LET varMaxDate = Num(Peek('OrderDate',-1,'Orders'));
LET varToday = Num(Today());
//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
//*************Master Calendar************
MasterCalendar:
LOAD
TempDate AS OrderDate,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear,
Inyeartodate(TempDate, $(varToday), 0)*-1 AS CurYTDFlag,
Inyeartodate (TempDate, $(varToday), -1)*-1 AS LastYTDFlag
Resident TempCalendar
Order by TempDate ASC;
Drop Table TempCalendar;
LET varMinDate = Num(Peek ('OrderDate',0,'Orders'));
LET varMaxDate = Num(Peek('OrderDate',-1,'Orders'));
LET varToday = Num(Today());
//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;
LOAD TempDate as OrderDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
Chr(81)& Ceil(Month(TempDate)/3) as Quarter,
QuarterName(TempDate) as QuarterName,
QuarterStart(TempDate) as QuarterStart,
QuarterEnd(TempDate) as QuarterEnd,
MonthName(TempDate) as MonthName,
MonthsName(3,TempDate) as MonthsName,
MonthStart(TempDate) as MonthStart,
MonthEnd(TempDate) as MonthEnd,
WeekYear(TempDate) as WeekYear,
Week(TempDate) & '-' & Year(TempDate) as WeakYear1,
InYear($(varToday),TempDate,1) as Inyear,
InYearToDate(TempDate,$(varToday), 0)*-1 AS CurYTDFlag,
Inyeartodate (TempDate, $(varToday), -1)*-1 AS LastYTDFlag,
$(varToday) as Today
Resident TempCalendar
Order by TempDate asc;
i hope this help you..
Sample doc..