topic Re: Master calender in QlikView

Master calender

Anonymous — Wed, 24 Dec 2014 06:32:05 GMT

Hi,how to create master calender please help me

Regards,

Nagendra

Re: Master calender

jagan — Wed, 24 Dec 2014 06:49:17 GMT

Hi,

Try like this

Transaction:

Load

FROM Transaction;

CalendarMaster:

LOAD

Date(InvoiceDate) AS InvoiceDate,

Year(InvoiceDate) AS Year,

'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,

Month(InvoiceDate) As Month,

Day(InvoiceDate) As Day,

Week(InvoiceDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(InvoiceDate) AS MinDate,

Max(InvoiceDate) AS MaxDate

RESIDENT Transaction;

Regards,

Jagan.

Re: Master calender

Wed, 24 Dec 2014 06:51:43 GMT

Hi Nagendar,

check this

Creating master calendar

Hope this helps.

Regards,

Barathiraja

Re: Master calender

Gysbert_Wassenaar — Wed, 24 Dec 2014 06:53:35 GMT

See this blog post: The Master Calendar and this document: The Fastest Dynamic Calendar Script (Ever)

Re: Master calender

alkesh_sharma — Wed, 24 Dec 2014 06:56:54 GMT

Easiest way of doing is to Take the resident of your transaction table and pull the date filed:

read more about creating a master calender on the below link:

Creating A Master Calendar

Re: Master calender

engishfaque — Wed, 24 Dec 2014 07:22:28 GMT

Dear Nagendar,

Here it is,

Day(DateFeild) AS CalendarDayOfMonth,

WeekDay(DateFeild) AS CalendarDayName,
Week(DateFeild) AS CalendarWeekOfYear,
Month(DateFeild) AS CalendarMonthName,
'Q' & Ceil(Month(DateFeild)/3) AS CalendarQuarter,
Year(DateFeild) AS CalendarYear,

WeekName(DateFeild) as CalendarWeekNumberAndYear,
MonthName(DateFeild) as CalendarMonthAndYear,
QuarterName(DateFeild) as CalendarQuarterMonthsAndYear,

DayStart(DateFeild) as CalendarDayStart,
WeekStart(DateFeild) as CalendarWeekStart,
MonthStart(DateFeild) as CalendarMonthStart,
QuarterStart(DateFeild) as CalendarQuarterStart,
YearStart(DateFeild) as CalendarYearStart,

DayEnd(DateFeild) as CalendarDayEnd,
WeekEnd(DateFeild) as CalendarWeekEnd,
MonthEnd(DateFeild) as CalendarMonthEnd,
QuarterEnd(DateFeild) as CalendarQuarterEnd,
YearEnd(DateFeild) as CalendarYearEnd;

Kind regards,

Ishfaque Ahmed

Re: Master calender

amit_saini — Wed, 24 Dec 2014 07:28:48 GMT

Hi,

Try this:

LET vMinDate = num(makedate(2014));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield as Date_Key%,

date(Datefield) as Date,

year(Datefield) as Year,

month(Datefield) as Month,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday

RESIDENT Datefield;

drop table Datefield;

Thanks,

Re: Master calender

its_anandrjs — Wed, 24 Dec 2014 08:07:38 GMT

Hi,

1. Questions is do you have single transactional date field in the table.

2. Master calendar based on single date field or it is multiple date.

For first you can try this

MinMaxDate:
Load
Num(Min(TransacDate)) as MinTransacDate,
Num(Max(TransacDate)) as MaxTransacDate
Resident Sales;

Let vMinTransacDate = NUM(Peek('MinTransacDate',0,'MinMaxDate'));
Let vMaxTransacDate = NUM(Peek('MaxTransacDate',0,'MinMaxDate'));
LET vTransacToday    = NUM(Today());
Let vNoOfDays = vMaxTransacDate - vMinTransacDate + 1 ;

Drop Table MinMaxDate;

TransacDateField:
Load
Date($(vMinTransacDate) + RowNo() - 1)    as TempTransacDate
AutoGenerate $(vNoOfDays);

TransacDateMasterCalendar:
LOAD
TempTransacDate                              as TransacDate,
Day(TempTransacDate)            as TransacDay,
WeekDay(TempTransacDate)        as TransacWeekday,
NUM(WeekDay(TempTransacDate))   as TransacWeekDayNO,
Week(TempTransacDate)           as TransacWeek,
Month(TempTransacDate)          as TransacMonth,
Num(Month(TempTransacDate))     as TransacMonthNO,
'Q' & ceil(month(TempTransacDate) / 3) as TransacQuarter,
Year(TempTransacDate)           as Year,
Date (MonthStart(TempTransacDate) , 'MMM YYYY') as TransacMonthYear

RESIDENT TransacDateField
ORDER BY TempTransacDate ASC;

DROP TABLE TransacDateField;

And for second read this posts

Canonical Date

http://community.qlik.com/message/541339#541339

Regards

Anand