Qlik Community

QlikView Documents

Documents for QlikView related information.

Master Calendar Generation Script

MVP
MVP

Master Calendar Generation Script

Below script helps in generating the Master calendar.  In this script I am getting the Minimum and Maximum dates in my Invoice table and generating the entries for each date in CalendarMaster table between minimum and maximum invoice date.

CalendarMaster:

LOAD

    Date(InvoiceDate) AS InvoiceDate,

    Year(InvoiceDate) AS Year,

    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,    

    Month(InvoiceDate) As Month,

    Day(InvoiceDate) As Day,

    Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(InvoiceDate) AS MinDate,

    Max(InvoiceDate) AS MaxDate

RESIDENT Invoice;

Hope this helps others in generating the Master Calendar.

Regards,

Jagan.

Labels (1)
Comments
Not applicable

When I use this and run (replace the InvoiceDate by AbcDate).. The data is not reloded and the ODBC connectivity is broken any comments?

seebach
Contributor III

Hey, if you don't mind, I've got a method here that is faster and more user friendly The Fastest Dynamic Calendar Script (Ever)

Not applicable

First time user and this was my first hurdle and you code solved it perfectly for me!  Just the sort of start I needed.  Thanks Jagan

Not applicable

Congratulations ! was very helpful.

bestofwest
Contributor II

Not a fast process.

Not applicable

This is the script I use:

LET varMinDate = Num(Peek ('OrderDate',0,'SalesOrderDetail'));

LET varMaxDate = Num(Peek('OrderDate',-1,'SalesOrderDetail'));

LET varToday = Num(Today());

//************TempCalendar***********

TempCalendar:

LOAD      $(varMinDate) + rowno() - 1 AS DateNumber,

               date ($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE $(varToday) - $(varMinDate) + 1;

//*************Master Calendar************

MasterOrderCalendar:

LOAD      TempDate AS OrderDate,

                Week (TempDate) AS OrderWeek,     

               Year (TempDate) AS OrderYear,

               Month (TempDate) AS OrderMonth,

               Day (TempDate) AS OrderDay,

               Weekday (TempDate) AS OrderWeekDay,

               'Q' & Ceil(Month (TempDate)/3) AS OrderQuarter,

               Year (TempDate)&'-Q' & Ceil(Month (TempDate)/3) AS OrderYearQuarter,

               Date( Monthstart (TempDate), 'MMM-YYYY') AS OrderMonthYear,

               Week (TempDate) & '-' & Year (TempDate) AS OrderWeekYear,

               Inyeartodate(TempDate, $(varToday), 0)*-1 AS OrderCurYTDFlag,

               Inyeartodate (TempDate, $(varToday), -1)*-1 AS OrderLastYTDFlag

Resident TempCalendar

Order by TempDate ASC;

Drop Table TempCalendar;

nihhalmca
Valued Contributor II

Helpful

rajkumart
New Contributor III

This script is very Basic.

abhijith28
New Contributor III

can someone explain me what does this below function do:

 

 Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Version history
Revision #:
1 of 1
Last update:
‎03-05-2013 01:48 AM
Updated by: