Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
/// Preparing Calendar QVD//
tmp_MaxDate:
Load min(OrderDate) as mindate,
max(OrderDate) as maxdate
FROM
[$(vInfozoneQVDDir)Orders.qvd]
(qvd) ;
LET vMinDate = Peek('mindate',0,'tmp_MaxDate');
LET vMaxDate = Peek('maxdate',0,'tmp_MaxDate');
LET vToday = $(vMaxDate);
LET vTTM_MinDate= $(vMaxDate)-365;
LET vPTTM_MinDate= $(vMaxDate)-730;
LET vPPTTM_MinDate= $(vMaxDate)-1095;
//LET vAsOfDate = num(Today())-1; delete this variable
drop table tmp_MaxDate;
//Calendar AutoGenerate from Minimum Order date to Maximum OrderDate So that way we will have all dates even though we don't have transactions//
TEMP_CALENDAR:
LOAD
Date($(vMinDate) + RowNo() - 1) AS OrderDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
////mapping tables
//MappingDOW:
//Mapping LOAD * INLINE [
// Day, Day of Week
// 1, 1
// 2, 2
// 3, 3
// 4, 4
// 5, 5
// 6, 5
// 7, 5
//];
//
//Creation of Master Calendar Using the AutoGenerate Calendar table by doing resident of TEMP_CALENDAR//
MasterCalendar:
LOAD
OrderDate,
Week(OrderDate) AS Week,
Year(OrderDate) AS Year,
Month(OrderDate) AS Month,
'Q' & ceil(Month(OrderDate)/3) AS Quarter,
Day(OrderDate) AS Day,
text(WeekDay(OrderDate)) AS WeekDay,
Month(OrderDate)*100+Day(OrderDate) as DayOfYear,
//ApplyMap('MapWeekDayNo',WeekDay(OrderDate)) AS WeekdayNo,
Date(monthstart(OrderDate), 'MMM-YYYY') AS MonthYear,
num(Month(OrderDate), '00')&'-'& Year(OrderDate) AS YearMonth_MMYYYY,
Year(OrderDate) & num(Month(OrderDate), '00') AS YearMonth,
WeekYear(OrderDate) & num(Week(OrderDate), '00') AS YearWeek,
if(Year(OrderDate)=WeekYear(OrderDate), Week(OrderDate) - Week(MonthStart(OrderDate)) + 1, Week(OrderDate) + 52 - Week(MonthStart(OrderDate)) + 1) AS [Week in Month], //week number within a month, 1, 2, 3, 4, 5, 6
InYearToDate(OrderDate, $(vToday), 0) * -1 AS CurYTDFlag,
InYearToDate(OrderDate, $(vToday), -1) * -1 AS LastYTDFlag,
InMonthToDate(OrderDate, $(vToday), 0) * -1 as CurMTD_flag,
InMonthToDate(OrderDate, $(vToday), -1) * -1 as LastMTD_flag,
num(Month(OrderDate)) as MonthNo,
QuarterEnd(OrderDate) as Quarterend,
if(OrderDate<=date($(vMaxDate)),if(OrderDate>date($(vTTM_MinDate)),1,null()),null()) as TTM_Flag,
if(OrderDate<=date($(vTTM_MinDate)),if(OrderDate>date($(vPTTM_MinDate)),1,null()),null()) as PTTM_Flag,
if(OrderDate<=date($(vPTTM_MinDate)),if(OrderDate>date($(vPPTTM_MinDate)),1,null()),null()) as PPTTM_Flag
RESIDENT TEMP_CALENDAR
ORDER BY OrderDate ASC;
DROP TABLE TEMP_CALENDAR;
left join (MasterCalendar)
LOAD WeekDay,
WeekDayNo INLINE [
WeekDay, WeekDayNo
Mon, 1
Tue, 2
Wed, 3
Thu, 4
Fri, 5
Sat, 6
Sun, 7
];
Store MasterCalendar into [$(vBaseQVDDir)MasterCalendar.qvd] (qvd);
drop Table MasterCalendar;
Sorry everyone , this is a non question one of my friend needs master calendar script. Only way is to post it as a discussion so that way he can access from the community. it was urgent.
What's your question?
Are you sharing the script to create the Calendar? What is the issue?
Could you share your requirement?
Sorry everyone , this is a non question one of my friend needs master calendar script. Only way is to post it as a discussion so that way he can access from the community. it was urgent.
Ah, ok. Well, when starting a new discussion you have an option to specify if it is a question or not. Next time simply choose the other option. Can you mark this discussion as answered? You can use the(Mark as assumed answered) option in your starting post.
Thank you Gysbert will do . Thank you for your guidance.