Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

master calendar

/// 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;

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

6 Replies
Gysbert_Wassenaar

What's your question?


talk is cheap, supply exceeds demand
Kushal_Chawda

Are you sharing the script to create the Calendar?  What is the issue?

senpradip007
Specialist III
Specialist III

Could you share your requirement?

Anonymous
Not applicable
Author

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.

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Thank you Gysbert will do . Thank you for your guidance.