topic Re: Master Calendar in QlikView

Master Calendar

NavinReddy — Wed, 19 Feb 2014 13:50:19 GMT

Dear All,

I want to display dates from 2008 to 2014

but this code will displaying only 2010 dates

tell some one help me

where i am wrong

set vDateMin = MAKEDATE(2008,1,1);

set vDateMax = MAKEDATE(2014,12,31);

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS CreatedDate,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Thanks In Advance

Niranjan

Re: Master Calendar

Nicole-Smith — Wed, 19 Feb 2014 13:53:31 GMT

The variables need to be numbers and you need to use let instead of set:

let vDateMin = num(MAKEDATE(2008,1,1));

let vDateMax = num(MAKEDATE(2014,12,31));

Re: Master Calendar

Wed, 19 Feb 2014 13:57:18 GMT

Date($(vDateMin) + RowNo() - 1) AS TempDate

Instead of this use this one

RowNo()-1+Floor($(vDateMin) As TempDate

Re: Master Calendar

MK_QSL — Wed, 19 Feb 2014 14:44:57 GMT

LET vDateMin = Num(MAKEDATE(2008,1,1));

LET vDateMax = NUM(MAKEDATE(2014,12,31));

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS CreatedDate,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Master Calendar

NavinReddy — Wed, 19 Feb 2014 16:02:28 GMT

guys again im getting same values

Re: Re: Master Calendar

Nicole-Smith — Wed, 19 Feb 2014 16:15:16 GMT

Your original code fixed with my first post gives me all dates from 1/1/2008 to 12/31/2014. See the attached file.

Re: Master Calendar

MK_QSL — Wed, 19 Feb 2014 16:26:55 GMT

There is nothing wrong with my code.

It's giving me All required dates.

Re: Master Calendar

NavinReddy — Wed, 19 Feb 2014 16:28:04 GMT

yes but did you check table viewer it shows like 2010

Thanks for your help full answer

Re: Master Calendar

Nicole-Smith — Wed, 19 Feb 2014 16:30:38 GMT

Table Viewer stops showing data after a certain number of rows. You're over the limit.

Re: Master Calendar

NavinReddy — Wed, 19 Feb 2014 16:37:41 GMT

Thank you so much smith

Re: Master Calendar

aveeeeeee7en — Wed, 19 Feb 2014 16:39:48 GMT

Hi Niru

See the Attachment.

It's very simple and easy to understand. No need of using complex logic and variables Stuff.

Regards

Aviral Nag

Re: Re: Master Calendar

rwunderlich — Wed, 19 Feb 2014 16:55:17 GMT

I agree with Nag that it's much easier to create the Calendar without using variables. Nag's example can be made even simpler by using preceding load.

MasterCalendar:

LOAD

Date AS CreatedDate,

Month(Date) AS CalendarMonth,

Year(Date) AS CalendarYear,

'Q'&ceil(Month(Date)/3) AS Quarters,

Month(Date) & '-' & Year(Date) AS CalendarMonthAndYear

;

LOAD

date(makedate(2008,1,1)+recno()-1) as Date

AUTOGENERATE makedate(2014,12,31) - makedate(2008,1,1) + 1;

-Rob