Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Calendar object or date handling error

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

date1.jpg

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?

date2.jpg

Any clues/assistance would be very welcome indeed

Thanx

Alexis

1 Solution

Accepted Solutions
Partner
Partner

Re: Calendar object or date handling error

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))

View solution in original post

6 Replies
MVP & Luminary
MVP & Luminary

Re: Calendar object or date handling error

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

- Marcus

Partner
Partner

Re: Calendar object or date handling error

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;

MVP & Luminary
MVP & Luminary

Re: Calendar object or date handling error

Try this:

MinMax:

LOAD floor(Max(AcceptDate)) AS MaxDate,

           floor(Min(AcceptDate)) AS MinDate

RESIDENT DeclarationHeader;

- Marcus

Partner
Partner

Re: Calendar object or date handling error

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!

BR

Alexis

Partner
Partner

Re: Calendar object or date handling error

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

Partner
Partner

Re: Calendar object or date handling error

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))

View solution in original post