topic Re: Calendar Object2 in QlikView

Calendar Object2

Tue, 16 Dec 2014 13:14:58 GMT

Hi,

I have a table with following columns.

Month Number	Month Year	YEAR
1	January	1990
5	January	1990
1	February	1990
4	February	1990

I need to create a DATE OBJECT (CALENDAR OBJECT) with these columns. Can anyone help me with this ?

Re: Calendar Object2

Anonymous — Tue, 16 Dec 2014 13:20:55 GMT

hi ,

First create master calender then use Date_Field as input in calendar object.

Re: Calendar Object2

Tue, 16 Dec 2014 14:01:01 GMT

How to create it ?

Which column, I should use for creating it...

Can Iuse this code for Master Calendar ?

MasterCalendar:

Load

TempDate As OrderDate,

Week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month

resident TempCalendar;

Re: Calendar Object2

Anonymous — Wed, 17 Dec 2014 10:45:48 GMT

Hi Praveen,

Use below code for master calender :

//Script One

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

If(weekday(TempDate)='Sat' or weekday(TempDate)='Sun',0,1) as PlannedBD2,

1 As PlannedBD,

weekday(TempDate) as CalDayStr,

Week(TempDate) AS CalWeek,

WeekYear(TempDate) AS CalTestWeek,

Year(TempDate) AS CalYear,

Month(TempDate) AS CalMonth,

year(TempDate)&'-'&Month(TempDate) AS CalYear_Month,

MonthName(TempDate) AS CalYearMonth,

Day(TempDate) AS CalDayNo,

'Q'&ceil(month(TempDate)/3) AS CalQuarter

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

If you want to create multiple calender objects then you should create data Island (separate table for each field for calendar object)

Pro_Start_Date:

LOAD TempDate AS _QA_Start_Date

RESIDENT TempCalendar ;

UAT_Start_date:

LOAD TempDate AS _UAT_Start_date

RESIDENT TempCalendar ;

Re: Calendar Object2

Thu, 18 Dec 2014 11:00:37 GMT

Thanks Balkumar.

And should I add the varMinDate and varMaxDate in SHEET PROPERTIES ?

I meant, Should I declare the values in Sheet properties like varMinDate = min(Month_Year) and varMaxDate= max(Month_Year)

Re: Calendar Object2

Anonymous — Thu, 18 Dec 2014 11:11:12 GMT

As your script loaded first so you need to assign varMinDate and varMaxDate in script itself. If you want to use min date and max date in your UI you can reassign the values to both variables in sheet as well for UI use.

Re: Calendar Object2

Thu, 18 Dec 2014 12:02:18 GMT

Actually, My doubt is, Like where am I declaring the Column name in the code ?

As per my table, MONTH_YEAR is the column, which has the Date information as "JAN 2011",... etc.

So Should I declare Month_Year as the column in place of Cal_Year ?

Re: Calendar Object2

Anonymous — Thu, 18 Dec 2014 12:34:31 GMT

Yes replace the columns as required.