Hi Everyone. I'm trying to modify my existing Master Calendar to show dates based on available data instead of showing dates of current calendar.
For example if i my data only contains datas of year 2012 the dashboard should only show 2012 as the maximum date but it is showing 2013 as the year.
My Qlikview document have normal date formats from SQL database and also a combination of Year and quarter for data from Excel spredsheet.
Please suggest on how i can do this or are there any other approach to this issue. Below are the code for master calender:
Calendar:
LET vDateMin = Num(MakeDate(2010,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
Date(TempDate) AS PostingDate,
Date(TempDate) AS InvestedDate,
Date(TempDate) AS InvestedDate2,
// Standard Date Objects
Day(TempDate) AS PostingDayOfMonth,
//Month
Month(TempDate) AS PostingMonth,
//Quarter
'Q' & Ceil(Month(TempDate)/3) AS PostingQuarter,
Ceil(Month(TempDate)/3) AS PQuarter,
//Year
Year(TempDate) AS PostingYear,
// Combo Date
Year(TempDate) & ' ' & Month(TempDate) AS YearMonth,
'Q' & Ceil(Month(TempDate)/3) & ' ' & Year(TempDate)as YearQuarter,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS PostingYearAndQuarterStaff,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS QuarterYear,
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS PostingYearAndQuarter
//Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS PostingYearAndQuarter
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
