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
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..
TestHelp.docx 11.1 K
Try using Make date function.
makedate(2010,01,01) as Period_date,
num(makedate(2010,01,01)) as Period_date_Num
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.
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.
ExQV.qvw 144.0 K