topic Re: Calendar object or date handling error in QlikView

Calendar object or date handling error

alexis — Wed, 24 Jul 2013 14:07:33 GMT

I created a Calendar table and the date field is contained in "AcceptDate"
I then created a Start Date ... End Date calendar controls with the following straightforard rules (see below screenshot)
The valid date range is 1/1/2006 to 24/7/2006 and these are defined in thevariables vMinDate and vMaxDate
The start and end dates selected are contained in variable "vStartDate" and "vEndDate"

Everything works find until I select a date that is outside the allowable range (e.g 25/7/2013)

When I select that date, the control follows the rules and correctly reverts the data back to the allowable maximum (23/7/2013)

Look, however, what happens to the variable that contains the vEndDate (see below) - Any ideas how to remove the time part of the date?

Any clues/assistance would be very welcome indeed

Thanx

Alexis

Re: Calendar object or date handling error

marcus_sommer — Wed, 24 Jul 2013 14:27:09 GMT

Is "AcceptDate" really an integer or a double value - floor(AcceptDate) creates an integer value.

- Marcus

Re: Calendar object or date handling error

alexis — Wed, 24 Jul 2013 15:00:51 GMT

Hi Marcus

Thank you for responding.

The following is an extract of the code that was used to generate the date field AcceptDate.

Does this help at all?

regards

Alexis

MinMax:

LOAD Max(AcceptDate) AS MaxDate,

Min(AcceptDate) AS MinDate

RESIDENT DeclarationHeader;

LET vMinDate = Num(Peek('MinDate',0,'MinMax'),'#####');

LET vMaxDate = Num(Peek('MaxDate',-1,'MinMax'), '#####');

DateIsland:

LOAD date($(vMinDate) + IterNo()) as D

AUTOGENERATE 1

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

Calendar:

LOAD

D As AcceptDate,

week(D) As Week,

weekday(D) As WeekDay

etc .....

RESIDENT

DateIsland;

Re: Calendar object or date handling error

marcus_sommer — Wed, 24 Jul 2013 15:24:46 GMT

Try this:

MinMax:

LOAD floor(Max(AcceptDate)) AS MaxDate,

floor(Min(AcceptDate)) AS MinDate

RESIDENT DeclarationHeader;

- Marcus

Re: Calendar object or date handling error

alexis — Wed, 24 Jul 2013 15:27:55 GMT

Thanks Marcus,

I will try it a little later because I am just about to go to a meeting and I will revert with result!

It sounds promising!

Alexis

Re: Calendar object or date handling error

alexis — Wed, 24 Jul 2013 20:21:49 GMT

Hi Marcus

Tried your suggestion but nothing has changed.

The "floor" function you suggested affects vMinDate and vMaxDate which if you look at my screenshots were not causing a problem anyway. It is the vEndDate (and possibly vStartDate) that appear to store the date in its full format (date and time).

Any other suggestions anyone?

Thanx

Alexis

Re: Calendar object or date handling error

alexis — Wed, 24 Jul 2013 20:47:07 GMT

Marcus

Hi again.

Your "floor" suggestion solved it in the end.

It seems that the offender was "AcceptDate" and so in the 2 calendar controls I have changed the Min and Max values as follows:

Start Date: (stored in variable vStartDate)

WAS CHANGED TO

Min value min(AcceptDate) floor(min(AcceptDate))

Max value max(AcceptDate) floor(max(AcceptDate))

End Date: (stored in variable vEndDate)

WAS CHANGED TO

Min value $(vStartDate) + 1 $(vStartDate)

Max value max(AcceptDate) floor(max(AcceptDate))