Qlik Community

QlikView Documents

Documents for QlikView related information.

Master Calendar Generation Script

MVP & Luminary
MVP & Luminary

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?

0 Likes
Luminary
Luminary

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

0 Likes
Not applicable

Congratulations ! was very helpful.

0 Likes
bestofwest
Contributor II

Not a fast process.

0 Likes
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

0 Likes
Partner
Partner

This script is very Basic.

0 Likes
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);

0 Likes
Partner
Partner

@abhijith28  see this help https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFun...

this is my script:

 

Let vToday = Today();

MasterCalendar: 
LOAD
MovimentiTestata.Doc_Data,
Week(MovimentiTestata.Doc_Data) as Week,
Year(MovimentiTestata.Doc_Data) as Year,
Month(MovimentiTestata.Doc_Data) as Month,
Day(MovimentiTestata.Doc_Data) as Day,
WeekDay(MovimentiTestata.Doc_Data) as WeekDay,
Dual('Q' & Ceil(Month(MovimentiTestata.Doc_Data) / 3), Ceil(Month(MovimentiTestata.Doc_Data) / 3)) as Quarter, //Dual for sorting
Date(MonthStart(MovimentiTestata.Doc_Data),'MMM-YYYY') as MonthYear,
Week(MovimentiTestata.Doc_Data) &'-'& If(Week(MovimentiTestata.Doc_Data)=1 AND Month(MovimentiTestata.Doc_Data)=12,Year(MovimentiTestata.Doc_Data)+1, If(Match(Week(MovimentiTestata.Doc_Data), 52, 53) AND Month(MovimentiTestata.Doc_Data)=1, Year(MovimentiTestata.Doc_Data)-1, Year(MovimentiTestata.Doc_Data))) as WeekYear,
InYearToDate(MovimentiTestata.Doc_Data, $(vToday), 0) * -1 as CYTDFlag,
InYearToDate(MovimentiTestata.Doc_Data, $(vToday), -1) * -1 as LYTDFlag,
If(DayNumberOfYear(MovimentiTestata.Doc_Data) <= DayNumberOfYear($(vToday)), 1, 0 ) as IsInYTD,
If(DayNumberOfQuarter(MovimentiTestata.Doc_Data) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,
If(Day(MovimentiTestata.Doc_Data) <= Day($(vToday)), 1, 0) as IsInMTD,
If(Month(MovimentiTestata.Doc_Data) = Month($(vToday)), 1, 0) as IsCurrentMonth,
If(Month(AddMonths(MovimentiTestata.Doc_Data,1)) = Month($(vToday)), 1, 0) as IsLastMonth;
 //**** Generate a temp table of dates ****
LOAD
Date(MinDate + IterNo() - 1) as MovimentiTestata.Doc_Data
WHILE MinDate + IterNo() - 1 <= MaxDate;
//**** Retrieve Min and Max dates from MovimentiTestata.Doc_Data field ****
LOAD
Min(FieldValue('MovimentiTestata.Doc_Data', RecNo())) as MinDate,
Max(FieldValue('MovimentiTestata.Doc_Data', RecNo())) as MaxDate
AUTOGENERATE FieldValueCount('MovimentiTestata.Doc_Data');

in the image the result

 

 

Annotazione 2019-03-14 114825.jpg

 

 

 

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