Master Calendar

Tue, 17 Nov 2015 15:08:55 GMT

Hi, I'm trying to create a master calendar and I loaded this statement:

Temp:

Load

min(FecSolicitud) as minDate,

max(FecSolicitud) as maxDate

Resident Orders;

//[Calendario]:

LET vDateMin = Num(Peek('minDate', 0, 'Temp'));

Let vDateMax = Num(Peek('maxDate', 0, 'Temp'));

//LET vDateMax = Floor(YearEnd(Today()));

LET vDateMax = Floor(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,'DD-MM-YYYY' ) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

num(WeekDay(Today()))as ppp,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'W' & Ceil(Week(TempDate)/8) as Fiscalweek,

Date(Date#('Mar Week 5','MMM WWW D'),'DD/MM/YYYY') AS Sunday,

Year(TempDate) AS CalendarYear,

Dual(Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3), Year(TempDate) & Ceil(Month(TempDate)/3)) as YearQtr,

'FY' & Right( Year(TempDate),2) as YearQtrfy,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(day(TempDate)) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

Month(TempDate)&' '& YearName(TempDate,0,2)as FiscalYear,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,

'Sun ' & DayStart(WeekStart(TempDate) + 3) as CalendarSunday

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());

but I have this error:

Unfamiliar words after sentencing

TempCalendar:

LOAD

42081 + Iterno()-1 As Num,

Date(42081 + IterNo() - 1) as TempDate

AutoGenerate 1

While 42081 + IterNo() -1 <= 42306,484606481

somebody can help me?

thanks

Re: Master Calendar

oknotsen — Tue, 17 Nov 2015 16:08:40 GMT

No clue about the actual question you are asking, but I would solve it differently anyway.

Use this:

AUTOGENERATE $(vDateMax) - $(vDateMin) +1;

I also would replace Iterno() by rowno().

Re: Master Calendar

pljsoftware — Tue, 17 Nov 2015 16:21:56 GMT

Hi Miguel,

you can do this very easy if you use the library RobWunderlich/Qlikview-Components · GitHub

Best Regards

Luca Jonathan Panetta

Re: Master Calendar

rwunderlich — Tue, 17 Nov 2015 18:40:53 GMT

It looks like your vDateMax variable is not getting set correctly. It contains a decimal portion, rather than the floor() value.

42306,484606481

-Rob

topic Re: Master Calendar in QlikView

Master Calendar

Re: Master Calendar

Re: Master Calendar

Re: Master Calendar