Calendar problem

[#1]

Hi,
I'm trying to create a calendar that ranges between the min (20101229)and max(20101230) dates I have in a table.
But, the following code doesn't work:

tmp:

LOAD
min(Date) as MinDate
max(Date) as MaxDate
RESIDENT Orders;
LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));

For some reason I get weird dates in Start and End variables.
For ex. End gets '20101487' - What's wrong?

Thanks

[Miguel_Angel_Baeyens]

Hello,

It's likely a conversion/formatting issue. Dates in QlikView are numeric values starting 31/12/1899 (Date 1). Try instead

LOADDate#(min(Date), 'YYYYMMDD') as MinDateDate#(max(Date), 'YYYYMMDD') as MaxDateRESIDENT Orders;LET Start = floor(YearStart(peek('MinDate')));LET End = floor(YearEnd(peek('MaxDate')));

Hope that helps

[#3]

Yes, that was the problem. Thank you very much!!!

[#4]

I'm going crazy I seem to have a problem again.

For some reason the variabes start and end in my code don't contain the dates and nothing helps!
(start and end variables should contain the starting and end dates, they contain weird numbers??..)

I attached the ex. and the output-I'd appreciate any help..
Thanks again

[#5]

Hi ,

Try using Make date function.

Example

Load

Id,

Sale,

makedate(2010,01,01) as Period_date,

num(makedate(2010,01,01)) as Period_date_Num

from Orders;

follow the steps

1)create 2 variables Vstart and Vend.

2)create calendar object, , In variables select Vstart, In Min values write exp Min(Period_date) and max values exp Max(Period_date)

3) create calendar object , follow step 2

4)Change format to date in Number Tab for both calendar object

5) Chart expression :

SUM(IF(Period_date_Num > Vstart AND Period_date_Num < Vend , Costs))

you will get the range of cost between the selected dates in calendar.

Regards,

Prabhu

[Miguel_Angel_Baeyens]

Hello,

Make sure that the date format from the excel sheet is YYYYMMDD. You can check this piece of script, based on yours, that should work

Data:LOAD * INLINE [OID, CID, DateA, 1, 20100101B, 2, 20100223C, 3, 20101205]; DateMinMax:LOAD Date(Date#(Max(Date), 'YYYYMMDD')) AS MaxDate, Date(Date#(Min(Date), 'YYYYMMDD')) AS MinDateRESIDENT Data; LET vMaxDate = Num(Peek('MaxDate', 0)); // This will return the date in QlikView numeric format 40517 which epoch is 1899/12/31LET vMinDate = Num(Peek('MinDate', 0)); // Use Date(vMinDate) in a text object and you should get the proper date representation DROP TABLE DateMinMax;

I've added a Date() representation function to the Date#() existing.

Hope this helps

[#7]

Thanks it really helped but now I'm not sure if I should perform a join between sorders table and Calendar table since choosing a date doesn't filter the correct orders.

See the attached code please.

Thanks again!

[Miguel_Angel_Baeyens]

Hello,

I think it should be as simple as change

Date(TempDate) AS CalendarDate,

by

Date(TempDate) AS Date,

Date from table "sorders" is the date field I'm assuming you want to use.

Hope this helps

[#9]

1. what about the other date format functions such as Day(TempDate) AS CalendarDayOfMonth ?That's why I thought to use join.

2. In addition the date list displays also numeric dates not only date format.

[#10]

OK I think it all works now (if I'm not missing aything..).
I attached the last file for example.

Thanks for all the help