Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Yes, that was the problem. Thank you very much!!!
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
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
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
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!
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
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.
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