Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
How to generate calendar for following dates
weekstart(1-1-2014) to weekend(31-7-2015)
I need the days between these two dates
My calendar should start from weekstart(1-1-2014) i.e from 30/12/2013 and end with weekend(31-7-2015) i.e 2nd august 2015
Thanks.
May be this:
Let varMinDate = Num(Date#('30-12-2013', 'D-M-YYYY'));
Let varMaxDate = Num(Date#('31-7-2015', 'D-M-YYYY'));
Let varMinDate = Num(WeekStart(Date#('1-1-2014', 'D-M-YYYY')));
Let varMaxDate = Num(WeekEnd(Date#('31-7-2015', 'D-M-YYYY')));
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
May be this:
Let varMinDate = Num(Date#('30-12-2013', 'D-M-YYYY'));
Let varMaxDate = Num(Date#('31-7-2015', 'D-M-YYYY'));
Let varMinDate = Num(WeekStart(Date#('1-1-2014', 'D-M-YYYY')));
Let varMaxDate = Num(WeekEnd(Date#('31-7-2015', 'D-M-YYYY')));
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Hi
Try like this
Let varMinDate = Floor(WeekStart(Date#('1-1-2014', 'D-M-YYYY')));
Let varMaxDate = Floor(WeekEnd(Date#('31-7-2015', 'D-M-YYYY')));
Calendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q'&Ceil(Month(TempDate)) AS Quarter,
WeekDay(TempDate) as WeekDay ;
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
See this example:
Thanks All for your precious time
Hi,
Please use this simple script as well.
LET vDateMin=num(makedate(2014,01,01));
LET vDateMax=floor(monthend(today()));
LET vDateToday = num(today());
Calender:
LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS CalendarDate,
month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarMonth,
year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarYear,
monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS CalendarMonthname
AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);