Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Master Calendar

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

3 Replies
oknotsen
Honored Contributor III

Re: Master Calendar

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

May you live in interesting times!
pljsoftware
Contributor III

Re: Master Calendar

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

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

42306,484606481


-Rob


Community Browser