master calendar

Anonymous — Fri, 15 Apr 2016 00:22:39 GMT

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

Re: master calendar

Gysbert_Wassenaar — Fri, 15 Apr 2016 05:28:37 GMT

What's your question?

Re: master calendar

Kushal_Chawda — Fri, 15 Apr 2016 05:32:25 GMT

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

Re: master calendar

senpradip007 — Fri, 15 Apr 2016 06:14:42 GMT

Could you share your requirement?

Re: master calendar

Anonymous — Fri, 15 Apr 2016 13:26:01 GMT

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.

Re: master calendar

Gysbert_Wassenaar — Fri, 15 Apr 2016 14:00:16 GMT

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.

Re: master calendar

Anonymous — Fri, 15 Apr 2016 15:17:59 GMT

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

topic Re: master calendar in QlikView

master calendar

Re: master calendar

Re: master calendar

Re: master calendar

Re: master calendar

Re: master calendar

Re: master calendar