MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, maxdate, 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, maxdate, -1) * -1 AS LastYTDFlag
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate
;
//=== Get min/max dates from fact table ===/
LOAD
min(Date)-1 as mindate,
max(Date) as maxdate
RESIDENT Facts
;
Change "Date" to the name of your date field and "Facts" to the name of your already loaded data table.
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
