Calender

[#1]

hi friends...

i am new in qlikview. please help me for building calender as i have three tables and all three tables having date columns with different names so how to build a calender as i have to consider all three date consider...

[tresesco]

You should go through these blog posts:

canonical-date

the-master-calendar

[senpradip007]

Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.

tempMaxDate:

Load

     Max(Date1) As MaxDate,

     Min(Date1)  As MinDate

Resident A;

Concatenate

Load

     Max(Date2) As MaxDate,

     Min(Date2)  As MinDate

Resident B;

Concatenate

Load

     Max(Date3) As MaxDate,

     Min(Date3)  As MinDate

Resident C;

MaxDate:

Load

     Max(MaxDate) As MaxDate,

     Min(MinDate)   As MinDate

Resident tempMaxDate;

Drop Table tempMaxDate;

Let vMaxDate = Peek('MaxDate', 0, 'MaxDate');

Let vMinDate  = Peek('MinDate', 0, 'MaxDate');

Cal:

LOAD

  Year(Date($(vMinDate) + RecNo() - 1)) As Year,

  Month(Date($(vMinDate) + RecNo() - 1)) AS Month,

  'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,

  Day($(vMinDate) + RecNo() - 1) AS Day,

  Date($(vMinDate) + RecNo() - 1) AS CalendarDate

AutoGenerate vMaxDate - vMinDate +1 ;

Drop Table MaxDate;

[#4]

Hi Ashish

you can use below code for the Calendar also you can go threw with the blog on Qlickview. as per your requirement you can create calendar control.

see the below generic code for your review. that i was using in my application. let me know if it work for you ore else i can create as per your requirement.

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Num(MakeDate(2001,12,31));

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 [Calendar Date],

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS Month,

num(Month(TempDate)) AS MonthNum,

Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate)))  as CalendarYYYYMM,

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

Year(TempDate) AS Year,

  // 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,

Date(MonthStart(TempDate)) as CalendarMonthStart,

Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

  // End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

  // 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

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[ashfaq_haseeb]

Hi,

use below code

Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));


//*************** Temporary Calendar ***************
TempCalendar:
LOAD 
  $(varMinDate) + rowno() - 1 AS Num, 
  date($(varMinDate) + rowno() - 1) AS TempDate 
AUTOGENERATE 
  $(varMaxDate) - $(varMinDate) + 1; 




//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
  Week(TempDate) AS Week,
  WeekYear(TempDate) as TestWeek,
  Year(TempDate) AS Year,
  Month(TempDate) AS Month,
  year(TempDate)&'-'&Month(TempDate) as Year_Month,
  MonthName(TempDate) as YearMonth,
  Day(TempDate) AS Day,
  'Q'&ceil(month(TempDate)/3) AS Quarter

RESIDENT TempCalendar 
ORDER BY TempDate ASC;


DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);

Regards

ASHFAQ

[#6]

thanx Pradip , i have tried this one but its showing error message as-

Script line error:

cal:

    LOAD

    Year(Date( + RecNo() - 1)) As Year,

      Month(Date( + RecNo() - 1)) AS Month,

      'Q' & Ceil(Month(Date( + RecNo() - 1))/3) As Quarter,

      Day( + RecNo() - 1) AS Day,

      Date( + RecNo() - 1) AS CalendarDate

    AutoGenerate vMaxDate - vMinDate +1

[ashfaq_haseeb]

Hi,

did you try my code.

Regards

ASHFAQ

[#8]

ya, i have tried this one also but its showing wrong output...

[#9]

Thanx but, its showing wrong output...

[senpradip007]

Try with this:

Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.

tempMaxDate:

Load

     Max(Date1) As MaxDate,

     Min(Date1)  As MinDate

Resident A;

Concatenate

Load

     Max(Date2) As MaxDate,

     Min(Date2)  As MinDate

Resident B;

Concatenate

Load

     Max(Date3) As MaxDate,

     Min(Date3)  As MinDate

Resident C;

MaxDate:

Load

     Max(MaxDate) As MaxDate,

     Min(MinDate)   As MinDate

Resident tempMaxDate;

Drop Table tempMaxDate;

Let vMaxDate = Peek('MaxDate', -1, 'MaxDate');

Let vMinDate  = Peek('MinDate', -1, 'MaxDate');

Cal:

LOAD

  Year(Date($(vMinDate) + RecNo() - 1)) As Year,

  Month(Date($(vMinDate) + RecNo() - 1)) AS Month,

  'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,

  Day($(vMinDate) + RecNo() - 1) AS Day,

  Date($(vMinDate) + RecNo() - 1) AS CalendarDate

AutoGenerate vMaxDate - vMinDate +1 ;

Drop Table MaxDate;