topic Re: Calendar in QlikView

Calendar

Sun, 02 Feb 2014 17:47:00 GMT

Hi All,

I have two Fields Start Date, End Date in my data.

Now i have to create two calendars using min date and max date from the Start Date, End Date fields.

I have taken two calendars and taken two variables for each calendar and used following expressions

Min({1} StartDate), Max({1} StartDate) in one calendar,

Min({1} EndDate), Max({1} EndDate) in another calendar

But i'm getting errors, Could you please somebody look into this.

Please find attachment of dates excel sheet.

Thanks in advance,

Chiru

Re: Calendar

MK_QSL — Sun, 02 Feb 2014 18:03:56 GMT

First of all, the date format is very strange in your uploaded file....

Some dates are of M/DD/YYY format...

Some dates are of MM/DD/YYYY format...

Also, let us know how you want to use these two calenders..

Are each Start Date is associated with End Date given in excel file?

If possible, please upload a sample apps.

Re: Calendar

maxgro — Sun, 02 Feb 2014 18:13:58 GMT

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Directory;

Source:

LOAD Date#([Start Date]) as [Start Date],

Date#([End Date]) as [End Date]

FROM

Dates.xls

(biff, embedded labels, table is Sheet1$);

// generate date calendar1 (min, max Start Date)

MinMaxDate:

LOAD min([Start Date]) as MinDate, max([Start Date]) as MaxDate Resident Source;

Let vMinDate = Peek('MinDate', -1, 'MinMaxDate') ;

Let vMaxDate = Peek('MaxDate', -1, 'MinMaxDate') ;

Calendar1:

Load Date(recno()+$(vMinDate)-1) as Date1

Autogenerate vMaxDate - vMinDate ;

drop Table MinMaxDate;

// generate date calendar2 (min, max End Date)

MinMaxDate:

LOAD min([End Date]) as MinDate, max([End Date]) as MaxDate Resident Source;

Let vMinDate = Peek('MinDate', -1, 'MinMaxDate') ;

Let vMaxDate = Peek('MaxDate', -1, 'MinMaxDate') ;

Calendar2:

Load Date(recno()+$(vMinDate)-1) as Date2

Autogenerate vMaxDate - vMinDate ;

drop Table MinMaxDate;

Re: Calendar

Mon, 03 Feb 2014 05:54:33 GMT

Hi Grossi,

This is working perfectly as per my requirement.

Thank you Very Very much

Thanks,

Chiru