topic Calendar problem in QlikView

Calendar problem

Thu, 30 Dec 2010 17:26:50 GMT

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

Calendar problem

Miguel_Angel_Baeyens — Thu, 30 Dec 2010 17:50:21 GMT

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

Calendar problem

Thu, 30 Dec 2010 18:07:58 GMT

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

Calendar problem

Thu, 30 Dec 2010 20:39:47 GMT

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

Calendar problem

Fri, 31 Dec 2010 06:31:25 GMT

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

Calendar problem

Miguel_Angel_Baeyens — Fri, 31 Dec 2010 08:34:14 GMT

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

Calendar problem

Fri, 31 Dec 2010 13:47:47 GMT

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!

Calendar problem

Miguel_Angel_Baeyens — Fri, 31 Dec 2010 14:04:16 GMT

Hello,

I think it should be as simple as change

Date(TempDate) AS CalendarDate,

Date(TempDate) AS Date,

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

Hope this helps

Calendar problem

Fri, 31 Dec 2010 14:16:56 GMT

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.

Calendar problem

Fri, 31 Dec 2010 15:41:34 GMT

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