topic Calendar Object - Numbers instead of Dates in QlikView

Calendar Object - Numbers instead of Dates

haymarketpaul — Thu, 16 Jul 2015 12:33:48 GMT

I've got a calendar based Rob Wunderlich's great Calendar Subroutine which works great

//===================================================
// Subroutine to Generate Calendar. 
//===================================================
SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
LOAD
    [$(_field)]
    ,year([$(_field)])                                 as [$(_prefix)Year]
    ,month([$(_field)])                             as [$(_prefix)Month]
    ,day([$(_field)])                                 as [$(_prefix)Day]
    ,weekday([$(_field)])                             as [$(_prefix)Weekday]
    ,month([$(_field)]) & ' ' & year([$(_field)])    as [$(_prefix)Period]
;
// Generate range of dates between min and max.
LOAD
    date(DateMin + IterNo()) as [$(_field)]        // Link Field
WHILE DateMin + IterNo() <= DateMax    
;
// Find min and max of date field values.
LOAD
    min(datefield)-1 as DateMin
    ,max(datefield) as DateMax
;
// Load date field values.
LOAD 
    FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');    

END SUB

I am trying to create 2 calendar objects so a user can select a FROM and TO registration date using this...

MinMaxDate:
Load 
    Min(Date([Registration Date])) as MinDate, 
    Max(Date([Registration Date])) as MaxDate 
Resident RegistrationCalendar;

Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));
Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

The calendar object seems to be populated with a 5 digit # rather than a date.

Do i need to do something to convert it?

Re: Calendar Object - Numbers instead of Dates

Anonymous — Thu, 16 Jul 2015 12:37:30 GMT

On the Number tab of your Calendar Object, Override Document Settings and set to Date.

Re: Calendar Object - Numbers instead of Dates

haymarketpaul — Thu, 16 Jul 2015 12:39:07 GMT

Perfect - Thank you

Re: Calendar Object - Numbers instead of Dates

haymarketpaul — Thu, 16 Jul 2015 12:51:34 GMT

I've got a new problem in that the data isn't filtered when i pick a date?

Have variables in the calendar object set to vMinDate and vMaxDate respectively in each of the objects

and then Min Value set to

=Min(Date([Registration Date]))

and Max Value set to

=Max(Date([Registration Date]))

Re: Calendar Object - Numbers instead of Dates

Anonymous — Thu, 16 Jul 2015 12:54:37 GMT

Probably best for you to to create a sample qvw for that and create a new thread for that new question.

Re: Calendar Object - Numbers instead of Dates

haymarketpaul — Mon, 20 Jul 2015 15:49:19 GMT

No problem - i was missing the triggers - all working nicely now