Dear all,
i have data from 13 june 2013 ,Qliksense takes the date 13,14,15 as 24th week but Qlikview takes 13,14,15,16 as 24th week.I am using the same calendar.I want the data to be same in both the qliksense and qlikview .
//**************Mastercalendar*************//
Calendar:
MinMax:
LOAD
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
RESIDENT patient ;
LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));
LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));
LET vToday = $(vMaxDate);
//*****Temp Calendar*****
TempCal:
LOAD
DATE($(vMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
//*****Master Calendar*****
Calendar:
LOAD
TempDate AS Date,
//if(Week(TempDate) = 53,'1',
//If(Year(TempDate) = '2016',Week(TempDate)+1,Week(TempDate) )) as Week,
Week(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
WEEKDAY(TempDate) AS WeekDay,
AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],
AutoNumber(YEAR(TempDate) & Week(TempDate), 'WeekID') as [WeekID],
'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) AS Quarter,
date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,
AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '.' & YEAR(TempDate) AS WeekYear,
date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,
//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,
//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag
INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,
INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag
//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,
//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
