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: 
Not applicable

Master Calendar

Hi Experts,

Can someone help me how to create Master calendar for the attached data

Thanks in Advance

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another calendar script:

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min([Start Date]) as MinDate,

    Max([End Date]) as MaxDate

Resident YourTable;

hope this helps

regards

Marco

View solution in original post

11 Replies
Chanty4u
MVP
MVP

hi,

by usng dis u can create..please chk below thread

https://community.qlik.com/message/685476#685476

engishfaque
Specialist III
Specialist III

Dear Jack,

Kindly find attached Master Calendar.

Kind regards,

Ishfaque Ahmed

Not applicable
Author

Hi,

Am using personal edition. Could you please paste the script

Thank you,

HirisH_V7
Master
Master

Hi,

Check this ,

https://community.qlik.com/docs/DOC-12743

For better Perfromance.

Hope this helps.

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable
Author

Hi,

Name the excel file load as T1 (shown in below image)

eqe.PNG

and paste following code in  Script:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

               min([Start Date]) as minDate, 

               max([End Date]) as maxDate 

Resident T1; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

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;

Regards,

Not applicable
Author

PFA.

prma7799
Master III
Master III

MarcoWedel

Hi,

another calendar script:

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min([Start Date]) as MinDate,

    Max([End Date]) as MaxDate

Resident YourTable;

hope this helps

regards

Marco