Kategorien
Allgemeines

QlikView Calendar Script

A small calender generation script

 
//GLOBAL CALENDAR
LET varMinDate = num(Floor(peek('Date',0,'FactTable')));
LET varToday = num(today());
LET varMaxDate = $(varToday)+370; //I only want to see the next 370 days
//LET varMaxDate = num(Ceil(peek('Date',-1,'FactTable')));

DateField:
LOAD $(varMinDate) + RowNo() - 1         AS Num,
date($(varMinDate) + RowNo() - 1)     AS Datefield
AutoGenerate
$(varMaxDate) - $(varMinDate) + 1;

QUALIFY *; UNQUALIFY ´%*´;
Calendar:
LOAD
num(Datefield)                                 AS %CALDate,
if(Datefield < MonthStart($(varToday)),1,0)    AS FullMonthsOnly,
Year(Datefield) & '/' & Month(Datefield) & '/' & Week(Datefield) AS Reporting_Date_Tree,
MonthName(Datefield)                           AS Month,
Month(Datefield)                               AS Monthname,
Week(Datefield)                                AS Week,
Day(Datefield)                                 AS Day,
DayNumberOfYear(Datefield)                     AS DayOfYear,
if(day(MonthEnd(Datefield))<=30,1,0)           AS ShortMonth,
if(day(MonthEnd(Datefield))>30,1,0)            AS LongMonth,
WeekDay(Datefield)                             AS Weekday,
Year(Datefield)                                AS Year,
Yearname(Datefield,0,5)                        AS FiscalYear,
InMonth(Datefield,$(varToday),0,5)*-1          AS CURRENTMonth,
inYearToDate(Datefield,$(varToday),0,5)*-1     AS YTDCurrentYear,
inYearToDate(Datefield,$(varToday),-1,5)*-1    AS PYTDLastYear
RESIDENT DateField ORDER BY Datefield ASC;

DROP TABLE DateField; UNQUALIFY *;

Schreibe einen Kommentar