Master Takvim, Qlik Script içerisinde loop kullanılarak, veri setinizdeki en erken tarihten en geç tarihe kadar her gün için tek bir kayıt oluşturularak hazırlanır.
MinMaxDate:
LOAD Min(TarihAlani) as MinTarih,
Max(TarihAlani) as MaxTarih
RESIDENT Fact;
LET vMinDate = Num(Peek('MinTarih', 0, 'MinMaxDate'));
LET vMaxDate = Num(Peek('MaxTarih', 0, 'MinMaxDate'));
DROP TABLE MinMaxDate;
CALENDAR_T:
load $(vMinDate)+ IterNo()-1 as DATE
AutoGenerate(1) While $(vMinDate)+IterNo()-1<=$(vMaxDate) ;
CALENDAR:
load
NUM(DATE) as %KeyDate,
date(DATE) as Date,
year(DATE) as Year,
// month(DATE) as Month,
NUM(month(DATE)) as MonthNum,
Week(DATE) as Week,
IF (MATCH(NUM(month(DATE)),1,2,3),'Q1',
IF (MATCH(NUM(month(DATE)),4,5,6),'Q2',
IF (MATCH(NUM(month(DATE)),7,8,9),'Q3',
IF (MATCH(NUM(month(DATE)),10,11,12),'Q4')))) as Quarter,
Date(Monthstart(DATE),'MM.YYYY') as "Month",
IF(DATE=MonthEnd(DATE),1,0) as Flag_MonthEnd,
WeekDay(DATE) as WeekDay,
// Year flags
inyear(DATE, $(vToday), 0) * -1 as CY, //Current year
inyear(DATE, $(vToday), -1) * -1 as FPY, //First prior year
inyear(DATE, $(vToday), -2) * -1 as SPY, //Second prior year
// Year-to-date flags
inyeartodate(DATE, $(vToday), 0) * -1 as CYTD, //Current year-to-date
inyeartodate(DATE, $(vToday), -1) * -1 as FPYTD, //First prior year-to-date
inyeartodate(DATE, $(vToday), -2) * -1 as SPYTD, //Second prior year-to-date
// Quarter flags
inquarter(DATE, $(vToday), 0) * -1 as CQ, //Current quarter
inquarter(DATE, $(vToday), -4) * -1 as FPQ, //First prior quarter, same quarter last year
inquarter(DATE, $(vToday), -8) * -1 as SPQ, //Second prior quarter, same quarter two years ago
// Quarter-to-date flags
inquartertodate(DATE, $(vToday), 0) * -1 as CQTD, //Current quarter-to-date
inquartertodate(DATE, $(vToday), -4) * -1 as FPQTD, //First prior quarter-to-date, same quarter last year
inquartertodate(DATE, $(vToday), -1) as FPQTD1,
inquartertodate(DATE, $(vToday), -8) * -1 as SPQTD, //Second prior quarter-to-date, same quarter two years ago
// Month flags
inmonth(DATE, $(vToday), 0) * -1 as CM, //Current month
inmonth(DATE, $(vToday), -1) * -1 as PM, //Previous month
inmonth(DATE, $(vToday), -12) * -1 as FPM, //First prior month, same month last year
inmonth(DATE, $(vToday), -24) * -1 as SPM, //Second prior month, same month two years ago
// Month-to-date flags
inmonthtodate(DATE, $(vToday), 0) * -1 as CMTD, //Current month-to-date
inmonthtodate(DATE, $(vToday), -1) * -1 as PMTD, //Previous month-to-date
inmonthtodate(DATE, $(vToday), -12) * -1 as FPMTD, //First prior month-to-date, same month last year
inmonthtodate(DATE, $(vToday), -24) * -1 as SPMTD, //Second prior month-to-date, same month two years ago
// Week flags (use addmonths() to ensure proper week)
// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function
inweek(DATE, $(vToday), 0) * -1 as CW, //Current week
inweek(DATE, addmonths($(vToday), -12), 0) * -1 as FPW, //First prior quarter, same week last year
inweek(DATE, addmonths($(vToday), -24), 0) * -1 as SPW, //Second prior quarter, same week two years ago
// Week-to-date flags (use addmonths() to ensure proper week)
// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function
inweektodate(DATE, $(vToday), 0) * -1 as CWTD, //Current week-to-date
inweektodate(DATE, addmonths($(vToday), -12), 0) * -1 as FPWTD, //First prior week-to-date
inweektodate(DATE, addmonths($(vToday), -24), 0) * -1 as SPWTD, //Second prior week-to-date
// Day flags (use addmonths() to ensure proper day)
inday(DATE, $(vToday), 0) * -1 as CD, //Current day
inday(DATE, addmonths($(vToday), -12), 0) * -1 as FPD, //First prior quarter, same day last year
inday(DATE, addmonths($(vToday), -24), 0) * -1 as SPD, //Second prior quarter, same day two years ago
// Additional Week Flags
inweek(DATE, $(vToday), -1) * -1 as W1, //One week ago
inweek(DATE, $(vToday), -2) * -1 as W2, //Two weeks ago
inweek(DATE, $(vToday), -3) * -1 as W3, //Three weeks ago
inweek(DATE, $(vToday), -4) * -1 as W4, //Four weeks ago
inweektodate(DATE, $(vToday), -1) * -1 as W1TD, //One week ago to date
nweektodate(DATE, $(vToday), -2) * -1 as W2TD, //Two weeks ago to date
inweektodate(DATE, $(vToday), -3) * -1 as W3TD, //Three weeks ago to date
inweektodate(DATE, $(vToday), -4) * -1 as W4TD //Four weeks ago to date
Resident CALENDAR_T;
Drop Table CALENDAR_T;