Master calendar issues

ajsjoshua — Wed, 25 Nov 2020 16:16:04 GMT

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;

topic Master calendar issues in QlikView

Master calendar issues